texts: Effect on msdb transaction log of running sp_delete_backuphistory for first time?

texts: Have a server with regular db backups but strangely sp_delete_backuphistory has never been scheduled so msdb is bigger than expected.  Backup history goes back 5 years in that table but only needs last 30 days of backup history. If we run sp_delete_backuphistory keeping only 30 days of history, is there any risk for the transaction log running out of space? MSDB is using simple recovery model. MSDB log file is currently 388544kb on C drive.  C drive has 45gb free.


texts: Install specific older version of postgresql centos

texts:
I need to rebuild a production system with same version of installed packages.

It's a Centos 5.11 and it was using this repo http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-6.noarch.rpm to install postgresql-server postgresql postgresql-odbc postgresql-plperl postgresql-contrib and postgis.

That repo doesn't exist anymore so I found this http://yum.pgrpms.org/8.3/redhat/rhel-5-i386/pgdg-centos-8.3-7.noarch.rpm that's versioin 8.3-7 and I need 8.3-6.

I can't seem to find the old version I need on the official website yum.pgrpms.org/repopackages.php

I don't know if old mirrors for the version I want exists or if I need to download each package individually matching the version that was listed on that one repo.


texts: Rewriting a query and eliminating subqueries

texts:
I have the following query :

SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs,
 num_bases_in_singletons
FROM stats_assembly WHERE ws_id IN (
  SELECT workflow_steps.id
  FROM workflow_steps, workflows
  WHERE workflow_steps.workflow_id = workflows.id
      AND workflows.sample_id = (
            SELECT workflows.sample_id FROM workflows WHERE workflows.id = (
                SELECT workflow_id FROM workflow_steps WHERE id = 462
            )
            AND workflow_steps.submodule_id = 2
      )
)

And I want to rewrite it without using subqueries. So far I have been able to do only this:

SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs,
  num_bases_in_singletons
FROM stats_assembly
INNER JOIN workflow_steps ON ( stats_assembly.ws_id = workflow_steps.id )
INNER JOIN workflows ON ( workflow_steps.workflow_id = workflows.id )

And I have no idea how to finish it.

Furthermore, this query potentially returns multiple rows but I want to get the row which has MAX(ws_id). Any tips will be greatly appreciated.


texts: Why aren't read-only queries executing concurrently?

texts:
I have a Django app using either sqlite or Postgres.  (I've tried both and encountered the same problem.)  Requesting a particular endpoint results in a read-only query like this that takes 1.5-3 seconds before the response.

The problem occurs when Django receives simultaneous requests to this endpoint, which is common in my application.  I expect Django to process the requests in parallel and still return a response in 3-4 seconds.  Instead, I get no response for any of the queries until 9-12 seconds.  In know that Django supports concurrent requests, so I suspect this is a database issue.

What am I missing?


texts: Database in postgresql with missing rows, how to fill the missing rows with a backup copy?

texts:
I have a big problem and I'm not that good with postgresql since I use Django to make queries and I only have the basics of sql for querys.

My problem is that there was a problem where a super user deleted a field that was related with my table "clients" (I don't know how she did it,  I didn't design the system, I just manage it), so there are like 120 clients that dissapeared from the table, I made a backup from yesterday (I know how important is to make backups) and I want to "copy" all the data from my backup to database with the missing rows.

I tried to restore only the table from pgadmin (the easy way) but it says that "duplicates key values violates unique constraint "user_client_pkey", detail: Key(id) = 461 already exist. So I'm guessing that I cannot restore because there are values that match the exact id's, is there any easy way (or program) that can copy the missing row only giving the id's and fill with the data that I have in my backup?

Something like compare rows and if there are no row  with that id, insert the data there? I know is not an easy query, but any help would be great.    The most easy way I'm sure that is to rollback, delete the database and restore, but there are so many tables that have more information and clients is the only one that really needs to be restore.

I tried to create a new table and restore the data from backup but it doesn't fill (probably because it doesn't match the name with the new tables), I also changed the name of my table with the missing rows and I create a new table and restore from the backup, but the pk got missing and I receive this error:

I changed the original of the table cliente, then I created a new table and name it like the original one, restore the database, it fills, but gives me this error:

Since the table public."admin_clienteB" doesn't have a primary key or OIDs, you can view the data only. Inserting new rows and changing existing rows isn't possible for the Edit Data tool without primary key.

Any helps would be great.


texts: Is it possible to do sums of aggregation that stops aggregating once the condition is reached?

texts:
Say I have a table amounts with millions of rows tallying up to some number, but I'm really just checking if it's > 100. Is there a way to stop aggregation since taking this:

SELECT SUM(amount)
FROM stored_value;

Into this:

SELECT SUM(amount)
FROM stored_value
HAVING SUM(amount) > 100;

However this does not change anything on the amount of aggregation. Something that would work with date ranges.


texts:
I have to deploy odoo with postgres database on amazon cloud. That i can do by simply setting up EC2 server and setting uo odoo on it. In case if internet in down, I want to be able to access same services and already saved data offline as well. For that I plan to install odoo with postgres database on my local machine (in my office) as well. Now I can access odoo services from anywhere (using cloud) when there is internet available. But in case if internet is down, I must be able to use locally installed odoo to get same services. For that purpose I need two things

Both databases should be exact replicas of each other.
On recovering from internet disconnectivity, I want the changes made in my local ( office) database reflect in the database at amazon cloud.
I am new to this stuff, kindly suggest the best possible approach (architecture) in this scenario.


texts: How to make UUID() function, statement-based replication safe?

texts:
I'm trying the copy and generating UUID()s on the fly for exiting data when executing A insert from select.

How can I make the UUID() function statement-based replication safe?
Can I somehow use the old ID's from the primary table or at least save them for the entire operation? so the replica will have the same IDs?

INSERT INTO table_name (number,REPLACE(UUID(),………(SELECT ….));

which means primary and any its slave have inconsistent data for table_name, because of the UUID func.


texts: Will SSIS 2012 work with SQL Server 2008R2 (not integrated)

texts:
Our company has SQL Server 2008R2 and plans to upgrade to 2012 in late 2013 or early 2014. I would like to use SSIS 2012 now as we do have the software.

We have a company policy where we cannot run SSIS packages on the database server itself. We run them from another app server, not integrated services.

My question is, if I were to install all 2012 composent for SSIS on our app server would there be any potental issues? I have not used SSIS 2012 yet so I do not know if the OLE DB compoents Id use to point to our SQL 2008 instance would be an issue.


texts: Finding the most commonly used non-stop words in a column

texts:
Simply put, I have a Postgres column containing ordinary prose and would like to determine the x most commonly used words ("word" being a group of characters delimited by a space, but not being stop word) across all rows.

I've found two solutions that nearly hit the mark:

SELECT *
FROM   ts_stat($$SELECT to_tsvector('english', title) FROM item$$)
ORDER  BY ndoc DESC
LIMIT  50;

This is great, except it returns word stems.

SELECT   UNNEST(string_to_array(title, ' ')) AS word, COUNT(*) AS ct
FROM     item
GROUP    BY 1
ORDER    BY 2 DESC
LIMIT    50;

This one returns the full words, but includes stop words.

For the sake of simplicity: stop words are supposed to be found on TABLE stop_words (lowercase_stopword text PRIMARY KEY).

Can anyone help me over the line?


texts:
From the implementation point of view for a database, is it preferable to handle optional form fields by setting to some default value (e.g. false, 0, "", etc.) or just ignore such fields (do nothing with the specific attribute)

So supposing you have a form of

firstname* (textbox)
lastname* (textbox)
What type of ice-cream do you like? (textbox)
If you like ice-cream, do you like flakes as  well? (checkbox)

Where firstname and lastname are required values (and suitably validated). In the form processing is it a "better idea"(TM) to automatically set values for icecream_type and flakes_are_the_best_thing_ever if no such values exit? Is it preferable for an implicitly boolean type such as the checkbox inputs, or indeed those relating to text, to have explicit false values instead of nulls?

Bearing in mind that prepared statements to mitigate against SQL injection will normally consider all aspects of the form for insertion

e.g. $pdoexec->prepare("INSERT INTO person (firstname, lastname, icecream_type, flakes_are_the_best_thing_ever")

While SQL statements will normally select all attributes from a particular table

SELECT firstname, lastname, icecream_type, flakes_are_the_best_thing_ever FROM person

At what point should these non-values be handled: pre-processing (before they get near the database) or post-processing (after the fact)?


texts: How can I allow an Oracle database to be accessed remotely?

texts:
I have an Oracle database installed on one machine, and I am trying to accessing the same from another machine.

I tried via Oracle SQL developer eclipse, and even via JDBC program, butnothing works. I am not sure if it is firewall issue (I have ruled out this because both the machines are in same domain) or something to do with Oracle database setting. I know in MySQL you need to grant permission to connect to database remotely. Do we need to set something similar on oracle SID also?

The error message I get:

  Io Exception: The Network Adapter could not establish the connection


texts: Why SQL Server doesn't support foreign key constraints on views?

texts: I know that SQL Server doesn't support using a view as the reference for a foreign key constraint, is there a reason (i.e. something to do with the relational model) this is the case?  It seems like it would be useful....


texts: Why is SQL Server consuming more server memory?

texts: SQL Server is consuming 87.5 % of my server RAM. This recently caused a lot of performance bottlenecks such as slowness. I researched this issue. One common solution I could find on the internet is to set the maximum limit for SQL Server. This was done and much improvement is gained. I want to know why if the maximum memory value is not set why SQL Server keeps consuming the resources


texts: Fix deleted transaction log file in SQL Server

texts: I have made the biggest mistake and deleted the transaction log file. Is there a way to fix this issue? I have the backup file, is there a way to restore it or reset everything again?


texts: PostgreSQL SELECT primary key as "serial" or "bigserial"

texts:
I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page.

If there is something wrong with my query I need to actually know what in a given scenario would throw it off on the explicit context of having a query or queries to run on a purely test database/table to modify that database/table so run this query on in order to test for any false-positives.

SELECT pg_attribute.attname,
format_type(pg_attribute.atttypid, pg_attribute.atttypmod),
CASE
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='bigint' THEN 'bigserial'
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='integer' THEN 'serial'
END AS type
FROM pg_index, pg_class, pg_attribute
WHERE pg_class.oid = 'delete2'::regclass
AND indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary;

Here is a table with a primary key that does not return the primary key with this query:

CREATE TABLE delete_key_bigserial (
  test1 integer,
  id bigserial NOT NULL,
  col1 text,
  col2 text,
  test2 integer
);


texts: Improving concurrency in MongoDB via sharding

texts:
Our current setup:

Cluster of ~500 ec2 c1.xLarge worker nodes with 8 threads on each running scientific simulations
Results temporarily stored at runtime on each worker node's local MongoDB, using ephemeral data storage.
Once a worker node finishes it's simulation jobs, it will stream the results to a master node (m2.2xlarge) which has a MongoDB instance using EBS Raid-0 for storage. Writing on the fly directly to the master DB was causing socket errors.
Data across the cluster is not necessarily independent, so the incoming data is coming in via upserts and $push, not inserts.
The worker node is terminated once it's data has been received by the master.
A typical run will generate ~100GB of data on the master, ~200,000,000 records.

Our problem is that we encounter severe write latency, causing our worker nodes to sit idly waiting on the master node write locks and page faults. We have been looking into setting up a sharded environment, and are looking for suggestions on how best to configure this for our use case.

Increasing our available memory via sharding seems like a logical step, but leaving a cluster of ec2 instances up can be significantly more costly than a single machine. It would be nice if we could use a sharded cluster at runtime, then replicate the results to a persistent single instance for durability.

EDIT: For a given run, our end goal is to have all results in a single collection on the monolithic mongo instance.


texts: Cannot get my slave - replication server to start

texts:
My replication server will not start. I have followed the instructions here:
http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/

As well as several other places including the Postgres Wiki as they all have the same information.

Here is what happens:
I do a full backup of the /9.2/data folder and move it to the replication/slave server, untar it. I can start PostgreSQL as well as pgAdmin and access all data with no problems.

I then move to the instructions on editing the pg_hba.conf and postgresql.conf for the slave server. I attempt to start it, and it fails (error in red [fail]. I cannot find any logs anywhere to give me a hint as to why.

I even verified there was no postmaster.pid in the data folder.

Also, I cannot find any log files. Do I need to "activate" a log file in the configuration?

So, if anyone wants to take a stab in the dark on my vague description, I'd love to hear any suggestions. I can put my conf files on pastebin if that will help.


texts: Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds

texts:
We have a number of tables (~1M records) that have a column on them defined as: [GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with newid(). We use this ID for synchronizing data across multiple systems, databases, file imports/exports, etc.

For this column, we have the following index created:

CREATE UNIQUE NONCLUSTERED INDEX [IxUx_XXXXXX_GlobalID] ON [dbo].[XXXXXX]
(
    [GlobalID] ASC
)

As expected, this index gets fragmented pretty quickly so we have to be on the ball with keeping it rebuilt. However, this is where we have a problem. My maintenance script that goes through and rebuilds all indexes over x% fragmented with ONLINE=ON always hangs forever on these indexes. I've tried to let one run for over 3 hours without success when the database was mostly to completely idle. Just for kicks, I even put the DB into single user mode and nothing changed. I also tried with ONLINE=OFF, again with no change. However, I am able to simply drop/create the index and it completes within ~15 seconds!

Just to make sure there's nothing funny with my maintenance script, I've manually tried rebuilding these indexes with no success. The script I use is: ALTER INDEX IxUx_XXXXXX_GlobalID ON [dbo].[XXXXXX] REBUILD WITH (ONLINE=ON) and it seems like it will never succeed. Given that I can drop and recreate it in only 15 seconds, 3 hours is WAY more than plenty to see this succeed on an idle database.

For now, I've altered my maintenance script to filter out indexes with "GlobalID" in the name and then have a follow-up script that drops/creates these indexes. This gets us by until we start having naming variations on these types of columns (i.e. we need a uniqueidentifier for some other purpose).

Any idea why rebuilding such indexes would never finish within a reasonable amount of time? I see this happen across ~12 tables, all with essentially the same column/index on them.


texts: JOIN between 3 tables to show missing data from 2 tables

texts:
I have 3 DB tables, a Computer table, a Patch table, and a ComputerPatched table. Below is a table mock up to assist my explanation....
DECLARE @Computer AS TABLE (
    [ComputerID] int
    , [ComputerName] nvarchar(50)
);

INSERT INTO @Computer ([ComputerID], [ComputerName])
VALUES (1, 'Computer A'), (2, 'Computer B'), (3, 'Computer C'), (4, 'Computer D');

DECLARE @Patch AS TABLE (
    [PatchID] int
    , [PatchName] nvarchar(50)
);

INSERT INTO @Patch ([PatchID], [PatchName])
VALUES (1, 'Security A'), (2, 'Security B'), (3, 'Security C'), (4, 'Security D');

DECLARE @ComputerPatched AS TABLE (
    [ComputerID] int
    , [PatchID] int
    , [InstallDate] datetime
);

INSERT INTO @ComputerPatched ([ComputerID], [PatchID], [InstallDate])
VALUES
    (1, 1, '20200708'), (1, 3, '20200115')
    , (2, 1, '20200615'), (2, 3, '20200615'), (2, 4, '20200215')
    , (3, 4, '20200427')
;

What I am trying to achieve is to JOIN all results from the Computer table AND Patch table to find out what patches have been installed on what computers and what patches have not been installed. The result I would be expecting would look like something as below...
+------------+--------------+---------+------------+-----------+
| ComputerID | ComputerName | PatchID | PatchName  | Installed |
+------------+--------------+---------+------------+-----------+
|          1 | Computer A   |       1 | Security A | 20200708  |
|          1 | Computer A   |       2 | Security B | NULL      |
|          1 | Computer A   |       3 | Security C | 20200115  |
|          1 | Computer A   |       4 | Security D | NULL      |
|          2 | Computer B   |       1 | Security A | 20200615  |
|          2 | Computer B   |       2 | Security B | NULL      |
|          2 | Computer B   |       3 | Security C | 20200615  |
|          2 | Computer B   |       4 | Security D | 20200215  |
|          3 | Computer C   |       1 | Security A | NULL      |
|          3 | Computer C   |       2 | Security B | NULL      |
|          3 | Computer C   |       3 | Security C | 20200427  |
|          3 | Computer C   |       4 | Security D | NULL      |
|          4 | Computer D   |       1 | Security A | NULL      |
|          4 | Computer D   |       2 | Security B | NULL      |
|          4 | Computer D   |       3 | Security C | NULL      |
|          4 | Computer D   |       4 | Security D | NULL      |
+------------+--------------+---------+------------+-----------+

I am having trouble envisioning how to write this query I believe I would need some form of OUTER JOIN but everything I have tried has failed. Any help would be much appreciated!


texts:
I need order my query for my subquery field.

Attempt:

 SELECT * from
 (
    SELECT *
    FROM (`multicategoria`)
    INNER JOIN `modelo` ON `modelo`.`id_MOD` = `multicategoria`.`id_MOD`
    INNER JOIN `categoria` ON `categoria`.`id_CAT` = `multicategoria`.`id_CAT`
    LEFT JOIN `media` ON `media`.`id_MED` = `modelo`.`id_MED`
    WHERE `categoria`.`id_CAT` =  '154'
    LIMIT 20, 4 )
order by x.id_CAT

But mysql output:

  (1248): Every derived table must have its own alias


texts:
I have a Large SQL Statement with several Join which need to be executed with different Where Conditions based on Procedure Input Parameters.

Ex:

 CASE pSearchType
  WHEN 1 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1 ;
  WHEN 2 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1
      AND Col2 = pInput2;
  WHEN 3 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1
      ORDER BY Col4;
  END CASE;

So is it advisable to have:
1) The SQL Statement as a View and Call the View from procedure with the Where clause and Order By added?
* Good for maintenance *

2) Or Have the SQL Statement inside each of the CASE statement?

Which one will be preferred way for performance?

PS: My procedure will have parameters that will have NULL (mean Optional) and those parameters will be used in where conditions.

Thanks.


texts: Can I use OpenLDAP to authenticate users in Oracle?

texts:
I like to use OpenLDAP to authenticate my Oracle users. It should be possible to fetch at least the password from the directory.

There appearently is something for the Oracle LDAP servers, but would it work with OpenLDAP (plain, from i.e. Debian)?


texts: Postgresql streaming replication slow on macOS

texts:
I am using PostgreSQL 10.1 on MAC on which I am trying to set up streaming replication. I configured both master and slave to be on the same machine. I find the streaming replication lag to be slower than expected on mac. The same test runs on a Linux Ubuntu 16.04 machine without much lag.

I have the following insert script.

for i in $(seq 1 1 1000)
do
bin/psql postgres -p 8999 -c "Insert into $1 select tz, $i * 127361::bigint, $i::real, random()*12696::bigint from generate_series('01-01-2018'::timestamptz, '02-01-2018'::timestamptz, '30 sec'::interval)tz;"
echo $i
done

The lag is measured using the following queries,

SELECT pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn();
SELECT (extract(epoch FROM now()) - extract(epoch FROM pg_last_xact_replay_timestamp()))::int;

However, the observation is very unexpected. The lag is increasing from the moment the transactions are started on master.

Slave    localhost_9001: 12680304        1
Slave    localhost_9001: 12354168        1
Slave    localhost_9001: 16086800        1
.
.
.
Slave    localhost_9001: 3697460920      121
Slave    localhost_9001: 3689335376      122
Slave    localhost_9001: 3685571296      122
.
.
.
.
Slave    localhost_9001: 312752632       190
Slave    localhost_9001: 308177496       190
Slave    localhost_9001: 303548984       190
.
.
Slave    localhost_9001: 22810280        199
Slave    localhost_9001: 8255144         199
Slave    localhost_9001: 4214440         199
Slave    localhost_9001: 0       0

It took around 4.5 minutes for a single client inserting on a single table to complete on master and another 4 minutes for the slave to catch up. Note that NO simultaneous selects are run other than the script to measure the lag.

I understand that replay in PostgreSQL is pretty simple like, "move a particular block to a location", but I am not sure about this behavior.

I have the following other configurations,

checkpoint_timeout = 5min
max_wal_size = 1GB
min_wal_size = 80MB

Now, I run the same tests with same configurations on a Linux Ubuntu 16.04 machine, I find the lag perfectly reasonable.

Am I missing anything?

UPDATE :

MAC Hardware configurations :
16GB RAM, 512GB SSD (Nvme)
Intel(R) Core(TM) i7-7820HQ CPU @ 2.90GHz

Linux hardware configurations :
16GB RAM, 512GB SSD (Nvme)
Intel(R) Core(TM) i7-6600U CPU @ 2.60GHz


texts: How to reduce network latency during mirroring?

texts:
We are using SQL Server 2008 R2 Standard edition for mirroring.

But we found out that network latency is too high. It takes nearly four times time to complete a transaction while mirroring is active. Is it possible to connect these two servers with a direct crossover cable and force sql server to use this link for mirroring?  Will that help?

We have already setup a crossover cable between these two servers. But how do we configure sql server to use this link for mirroring?

Any help will be appreciated.


texts: Setting isolation level at database level

texts:
We have a data warehouse that's being dropped and reproduced every night. We have lots of SSRS reports that use the warehouse. Sometimes an SSRS report goes wrong and will never execute, thus preventing our warehouse from being filled up.

I can easily go with dirty reads for that one (data isn't changing anyway) so I figured out we can just go to READ UNCOMMITTED transaction level. And now I've encountered a problem I simply cannot believe:

Is it really not possible to change a database's default isolation level? What would be the reason for that?

It just doesn't make sense. I want my default isolation level to be READ UNCOMMITTED. I really don't want to go to each SSRS report and set it's transaction isolation level separately. We have lots and lots of SSRS reports.

I could enable RCSI but it will only unnecessarily slow the database down. Handling the reports in such a way is pretty much impossible. We have lots of SSRS reports that are written very poorly (in the graphical designer...) and we don't have time to fix them all.

I'm going through the process when in abundance of free time but that doesn't happen often. Having the warehouse not fill up is a very serious problem to us. For now all our problems could be fixed by changing the default isolation level.

The data warehouse is being filled up at night when nobody is using it
The whole day no data gets into the DW and we work with offline data up to -1 day.

So in our model there's really no concurrent writing and reading unless a query gets stuck and somebody forgets about it and leaves it trying to run.

Any solutions?


texts: Sql Server 2016 Cannot drop database (it is really, really, really stuck)

texts:
I have a database that was involved in a mirroring session and something crashed hard and the database is stuck 'in recovery' and will not allow me to drop it because it thinks the database is in use although I can't see what.

I run this and the affected database is not listed:

SELECT DB_NAME(dbid) as 'DBName'
     , loginame as 'Login'
     , COUNT(dbid) as 'Connections'
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

I also run EXEC sp_who2 and the database is not listed either

I run

ALTER database careplans set offline with ROLLBACK IMMEDIATE;
DROP database careplans;

and I get an error "ALTER DATABASE failed because a lock could not be placed on database 'careplans'. Try again later."

This brings back no results:

select * from sys.sysprocesses where dbid = DB_ID( 'careplans')

This gives me the 'lock could not be placed' error:

ALTER DATABASE careplans
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

This gives me an "Exclusive access could not be obtained because the database is in use." error:

RESTORE DATABASE CarePlans WITH RECOVERY

Any of the following give me the 'lock could not be placed' error:

ALTER DATABASE careplans SET  SINGLE_USER WITH NO_WAIT
ALTER DATABASE careplans SET EMERGENCY;
DBCC CHECKDB (careplans, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE careplans SET online;
ALTER DATABASE careplans SET  Multi_USER WITH NO_WAIT

I have no idea what else I can try...?


texts: What is the preferred method to roll out updates to hundreds of databases

texts:
I am trying to determine the best way to roll out SQL scripts to hundreds of distributed servers.  I have a slow SSIS package and a very fast OSQL/BATCH process.

I am on a project where we have several hundred database servers deployed in the field.  I need to be able to routinely query the servers and/or apply database upgrade scripts.

When we only had 20 servers, I built an SSIS package to grab a list of servers and process them one at a time.  The SSIS package works great, has tons of error handling and logging, etc.  It is able to capture reult sets from each server and store them in a central location for further analysis.  Due to the way I built it, however, it processes each server serially.  20 Servers takes about 10 minutes to process.  I am at a point where this method is simply too slow.

Before I invest more time into re-writing this package to spawn other packages (one for each server), I was hoping to get some advice from others out there who faced a similar situation.

Would you use SSIS in this manner, or switch to powershell, or even OSQL?

Almost as a joke, I dug through my archives and pulled out a batch process I wrote 12  years ago to apply scripts on remote servers.  I made a few tweaks (changed to OSQL) and it runs incredibly fast.  I can process 100 servers in under 20 seconds.  I launch it from a windows 7 machine, which does not choke on that many command windows being opened.

The downside to the OSQL/BATCH solution is that the batch file is hard-coded with servernames and passwords.  But it is so fast I will continue to use it until I find a better solution.

SSIS Package - The current SSIS package does the following:
 - connect to a central server
 - retrieve a list of servers that need to be queried
 - using a forloop container, connects to each server (in series) and executes the query
 - if the query returns a result set, this information is captured and stored at central server
 - the central server is updated to reflect success/fail of each server
 - all errors are logged at the central server


texts:
I basically want to query this table:

CREATE TABLE tbl (id, thing, price, type);
AS VALUES
  (1, 'apple', 1.00, 'fruit'),
  (2, 'orang', 1.00, 'fruit'),
  (3, 'banan', 1.00, 'fruit'),
  (4, 'potat', 2.00, 'veg'),
  (5, 'carro', 2.00, 'veg'),
  (6, 'onion', 2.00, 'veg');

I want to grab the ids of records with the same attributes (in this case, price and type) so 1, 2, 3 and 4, 5, 6.

I don't want to have to specify the $1.00 or fruit values, just group by whatever shows up in those columns, if that makes sense.


texts: Programmatically determine whether a database was shut down cleanly or whether it required crash recovery

texts:
There are various ways to shut down the SQL Server service (I can think of at least: SHUTDOWN, SHUTDOWN WITH NOWAIT, stopping the service (including bugs), killing the process, power loss).

An unclean database shutdown causes crash recovery to run when the server starts up again. I would like to track events when crash recovery was needed. I'd like to look into those cases.

How can I query a running SQL Server instance to see what databases underwent crash recovery on the last server startup (or the last time they were brought online)? In order to automate this the query must not be a manual procedure.

I did not find anything in sys.databases. In particular, is_cleanly_shutdown apparently does not represent what I'd like to find out.


texts: After SSRS to PowerBI Reporting Server migration, parameters are rendering goofy when trying to enter data

texts:
We just successfully migrated a SSRS 2016 instance to PBIRS. Users are reporting weird behavior when trying to enter data in a multi value parameter.

When they click on a parameter to enter in data, normally a drop down text entry appears and they type in multiple values. That still happens, but the box goes renders to the bottom right of the screen and you only see that box on the screen (see picture) This happens in all web browsers.

EDIT: It appears that the box is showing where the parameter is supposed to show up if the report is full screen. I cannot see all of the parameters like we used to in SSRS


texts: Select a table's current identity from another database

texts:
How can I get the current identity of a table using OPENQUERY in SQL Server?
How to select the current identity on the table from another database?


texts: SELECT Data Set Using Stored Procedure with BCP Error

texts:
All, I have a BCP export query. It is failing with the useless error mesage on BCP usage:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]
NULL

The query I am using is as follows

DECLARE @SQL VARCHAR(8000);
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';"
               queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt"
               -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + '';
EXEC xp_cmdshell @SQL;
GO

where if I replace EXEC ispsSelectEmptyAsNull ''B1A''; with SELECT * FROM B1A; there is not problem. The query EXEC ispsSelectEmptyAsNull ''B1A''; itself runs with no problems and returns the correct result set. I have run a million BCP queries but never using an SP to provide the result set. Am I doing this correct?

The SP is as follows:

IF EXISTS (SELECT name
           FROM sys.procedures
           WHERE name = N'ispsSelectEmptyAsNull')
DROP PROCEDURE ispsSelectEmptyAsNull;
GO
CREATE PROCEDURE ispsSelectEmptyAsNull @TableName NVARCHAR(256)
AS
DECLARE @Columns VARCHAR(MAX);
SELECT @Columns =
    COALESCE(@Columns + N',
    NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '',
  N'NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '')
FROM (SELECT DISTINCT COLUMN_NAME
      FROM [IPACostAdmin].INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = @TableName) AS H
ORDER BY COLUMN_NAME;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
    SELECT ' + @Columns + N'
    FROM ' + @TableName + N';';
-- SELECT CONVERT(XML, @SQL);
EXEC (@SQL);
GO

Thanks very much for your time.

Ps. I do not care about SQL injection etc.


texts: Am I using the ORDER BY clause incorrectly

texts:
I am trying to enumerate a database, created specifically for the purpose of learning SQL injections. I am trying to discover the number of columns a particular select statement might be using. I do this by "Ordinal" ordering the result set. So, I start with ORDER BY 1, .. BY 2, and so on until I get an error returning 'Unknown column'. Now, for an injection like so:

localhost/sqli?id=2' order by 7 AND  '1

The resulting SQL statement is:

mysql> SELECT * FROM table_name where id='2' order by 7 and '1';

This returns a result set, but I was expecting it to throw an error returning "unknown column '7' in 'order clause' ". Why does it not throw the error. The table that I am working with has only 3 columns.


texts: Amazing improvement of script execution speed when split - not understood

texts:
I have an ETL job that merges data into about 20 columns of a fact table, all integer non-null (default 0) columns. There are 8 merge statements to completely update all the columns and they involve quite a lot of calculation and rows. I put all the merge statements into one long stored procedure and ran it, but it took an impossibly long time. I made some covering indexes and some other changes, which made barely any difference; I was still manually stopping the job after over an hour of running.

Acting on general intuition, I split the long script into seven separate stored procedures and called them one by one with an over-arching stored procedure. The whole lot consistently finishes within 3 minutes (no code changes were made to the scripts).

My question is this - is there a general set of conditions in which it is always so much significantly faster to split a script up into smaller steps - is there some feature of the scripts / server memory or other factor that will mean splitting is occasionally so much more effective than at other times?

Alternatively, is it just that it is always better to split scripts up like this (assuming no problems with naming / SP proliferation etc), and the huge difference in speed is nothing unusual?

My original script was large and would be hard to sanitise sufficiently to show here, but I can give some details:  It is about 550 lines, there were many derived tables, CTEs, and GROUP BY clauses, with a couple of relatively complex WHERE clauses and 1 temp table. There were 8 Merge statements, all targeting the fact table, but sourcing from 3 databases on the same server as the target. Maybe there is something in this which would have particularly slowed things down.


texts: Why execution plan could be skipped from XE session?

texts:
Got an issue, similar to this one: Why does SQL Server Profiler trace not capture SELECT operator in execution plans?. There was an advice to use XE instead of Trace, but it does not help.
Thing is, the same problem repeats on SQL Server 2016 with Extended Events.

I need to investigate a query from within a batch. This batch involves some temporary tables, so I can’t just catch a query from the middle and put it into Management Studio, Plan Explorer or any other app. So, the next idea was to watch it “in a wild”, gathering all the needed telemetry.

At first, I’ve set a Trace with “RPC completed”, “SP Statement completed” and “Showplan XML Statistics Profile” events. Everything went great except… I didn’t get just one plan – the plan I needed – because it was “skipped”.

Then I set an XE session with the same events: “rpc_completed”, “sp_statement_completed” and  “query_post_execution_showplan”. I set “no data loss” policy (I know, it hurts, but I needed answers and it’s dev-box anyways). But… still the same result.

All the sessions had 2 filters: by application name and by database id. I can’t see any good reason, why some plan just gets “skipped”. Maybe, someone can point me at what I am missing?


texts: Need help in picking solution and understanding when to add columns vs EAV

texts:
I've been working on the following problem for a long time and I can't decide between two solutions. While both would work to solve the problem, neither "feels" exactly right to me.

Any advice on this would be greatly appreciated. Also note that I'm not particularly great with DB design so any feedback is appreciated.

I have the following table:

ProductYearDistrictCrop (+-2 million records)

oid | productId | yearId | districId     | cropId | payDate     |
1   | 1         | 1      | 1             |1       | 2018-01-01  |
2   | 1         | 1      | 1             |1       | 2018-02-01  |
3   | 2         | 1      | 1             |1       | 2018-03-01  |
4   | 2         | 1      | 1             |1       | 2018-04-01  |

Some background info on the table:

A record is created for each possible combination of Product - Year - District - Crop and there is a unique constraint on those Foreign Keys (the combination must be unique). I opted to use Oid as the PK instead of a Composite Key because Oid can then be used by other tables as a FK and I assumed a lookup on the Oid would be quicker than a composite key.

For each of these records, there are additional options that can be picked from. These options come from the below table

Options (16 records)

oid | description   | systemDefaultValue
1   | Option 1      | 0.5
2   | Option 2      | 2.5
3   | Option 3      | 1.5
4   | Option 4      | 1.5

For each of these choices I need to store a custom value or just reuse the systemDefaultValue per record in ProductYearDistrictCrop

The two possible solutions I come up with are

Solution A : Simple add each possible option as column to the table

oid | productId | yearId | districId     | cropId | payDate     | Option1   | Option2   |
1   | 1         | 1      | 1             |1       | 2018-01-01  | 0.5       | 2.5       |
2   | 1         | 1      | 1             |1       | 2018-02-01  | 0.5       | 2.5       |
3   | 2         | 1      | 1             |1       | 2018-03-01  | 0.5       | 2.5       |
4   | 2         | 1      | 1             |1       | 2018-04-01  | 0.5       | 2.5       |

Which means we'll end up with 16 extra columns in ProductYearDistrictCrop and of course, each time a new option is added to the system, I need to update this table as well. While this doesn't happen often, it can happen.

The other consideration is that there are other tables that will also follow a similar design so that means I would have to update several tables each time a new option is added.

Solution B : Use what I think is an EAV approach for the options by creating a linking table

ProductYearDistrictCropOptionChoices

ProductYearDistrictCrop  | optionId  | customValue |
1                        | 1         | 0.5         |
2                        | 2         | 1.5         |
3                        | 3         | 100         |
4                        | 4         | 25          |

In this scenario I end up with a +- 32 million records. The obvious upside is that no db changes are required each time a new option is added. Also I'm working with worst case scenario so it's not that each record from ProductYearDistrictCrop WILL have 16 records linked to it.

My Feedback

My gut feeling to pick Solution A and add the columns for each option. I can then do a single lookup when I need the data and get everything I need from that row or sets of rows.

The other reason I prefer Solution A is that to my understanding attributes should be columns in a database. It's just that another table is already storing those attributes as options to be used by the system when building UIs and providing hte user with default options and values.

I've been wrestling with this for a long time and would just like to get some external advice and insights. Also it would help to get more opinions as I would like to justify my choice with a stronger reasoning than "I feel this one is good and that one is icky"


texts: Is this surprising deadlock reasonable or a MariaDB bug?

texts:
I recently had to diagnose a deadlock in an application that seemed impossible to me. Even after I found out how to reproduce it, I don't really undestand why it happens. I'm considering filing a MariaDB bug, but since I'm not a database expert, I thought I'd ask first if someone can tell me there is a reasonable explanation.

Edit: Filed as a bug now at https://jira.mariadb.org/browse/MDEV-17512

I used a recent version of mariadb (will have to look up the exact version number, but I think that's more interesting for a bug report than for a general "why does this happen") with its default transaction isolation level of REPEATABLE_READ. Here is how I can reproduce the problem:

First, let's set up the test:

create database deadlock;
use deadlock;
create table foo(id int primary key) engine=InnoDB;
insert into foo values (10);

Now we can start. Let's open two connections A and B to the database.

-- Connection A
begin;
delete from foo;

-- Connection B
begin;
delete from foo;

At this point, the delete issued by B is waiting. This is as expected because A is holding the relevant locks.

-- Connection A
insert into foo values (9);

And at this point, mariadb kills the waiting query of B due to a deadlock. This is very surprising to me, because it would mean that B has already managed to acquire some locks.

As an additional point of information, if you use the id 11 instead of 9 in the last statement, there is no deadlock.

So can someone explain what is happening here? Is this normal behaviour, or should I file a bug?

Edit:
Here is the output of SHOW ENGINE INNODB STATUS:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-10-15 21:33:09 0x7f9f9da02700
*** (1) TRANSACTION:
TRANSACTION 481736, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3049, OS thread handle 140323520444160, query id 12527706 localhost root updating
delete from foo
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481736 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** (2) TRANSACTION:
TRANSACTION 481731, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3050, OS thread handle 140323521046272, query id 12527773 localhost root update
insert into foo values(9)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481731 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 32882 page no 3 n bits 72 index PRIMARY of table `deadlock`.`foo` trx id 481731 lock_mode X locks gap before rec i
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000759c3; asc     Y ;;
 2: len 7; hex 2d000002182fc2; asc -    / ;;

*** WE ROLL BACK TRANSACTION (1)


texts: Why does SQL Server return some rows while still executing the query, and sometimes not?

texts:
There are queries where when we hit "execute", it shows some rows and it keeps growing, but the query is not over yet. Yet sometimes, it waits until the end of the query.

Why does this happen? Is there a way to control this?


texts: Oracle: REGEXP_LIKE: Strings with angle brackets aren't found (whether they are escaped or not)

texts:
The following question will answer my original question, plus many others. What would the correct syntax be to do a REGEXP_LIKE search (in Oracle Database 12c) for this string (which exists somewhere in a CLOB)? In other words, pretend like you want to find this exact string:

%.,"'?!:#$&)(*;+-/<>=@[]\^_{}|~

using:

"select * from my_table where regexp_like (some_column, '" + regexSearchString + "')"

Now, from everything I've read, you would need to transform the string by escaping the necessary characters  (\  ^  .  $  |  (  )  [  ] *  +  ?  {  }  ,) to get it to work, like this:

%\.\,"'\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~

Now I haven't tested every non-escaped character like &, _, or %, but I have found that searches with < or > are not found. Even though REGEXP_LIKE is not needed in the following example, say I wanted to find all occurrences of ><div class=, for whatever reason. Is there an additional thing that needs to be done to get it to correctly treat angle brackets just like any other letter?


texts: How does objectproperty() figure out if a function is deterministic in sql server?

texts:
How does sql server determine if a function is deterministic or not?

Consider the following function, I think it's deterministic (as it's ultimately an integer operation), but sql server returns 0 when I call the objectproperty method on it.

Understanding how it works will help me understand why I'm wrong in my assessment of this function.

CREATE FUNCTION dbo.EndOfPreviousMonth ( @Date date )
RETURNS date
AS BEGIN
    RETURN DATEADD(day, 0 - DAY(@Date), @Date);
END;

SELECT dbo.EndOfPreviousMonth('2010-01-01'); --Usage example

--Returns 0, I expected it to return 1
SELECT OBJECTPROPERTY( OBJECT_ID( 'dbo.EndOfPreviousMonth' ), 'IsDeterministic' );


texts: Derby slows down after 1.2 million records

texts:
I'm using a Derby database, mass insertions slow down to 1/4 to 1/6 the speed once there are 1.2 million records (about 6 GB database size, one major table, other tables are tiny). Is that normal?  Is there something I can do to tweak it and make it run faster?  I compressed the main table.
Questions

Should I consider other databases?
How could I compare Derby's performance, with, say, MySQL in this kind of scenario?
Are there such comparisons already?

(Running on Windows 7-based server R2, 16GB RAM, 8-core machine).


texts: Binlog - 'Race-like' Condition on Replication

texts:
I have a typical master-slave MySQL setup. Slave is in test mode currently. Master is heavily used.

I am noticing something that looks like a potential race-like condition on the slave. On occasion, because of heavy use on the master, I will get 2 entries in the Master binlog with the same timestamp on a very heavily used table.

On the slave, the relay log is identical to the binlog on the Master so I know that part is working properly. However, when these statements are replayed I end up with a 1062 Duplicate error condition.

When I check the slave tables I get the first statement replayed properly but only part of the second. The binlog format is currently mixed.

I am considering testing the setup with row-based instead of mixed mode replication as I have read that it is suppose to help with such a condition - I have not used RBR myself before.

Has anyone else had similar experiences?
Do you have a resolution to this condition?
Since RBR can have an impact on performance, is that significant from your experience?

Here were my settings:

MySQL ver is 5.5.13
Sync_binlog=1
innodb_flush_log_at_trx_commit=1

Thanks


texts: How to remove a table from a FOR ALL TABLES logical replication PUBLICATION in PostgreSQL

texts:
I created a logical replication PUBLICATION using FOR ALL TABLES. Now I've realized I don't want to replicate all of the tables after all. I tried dropping one of the tables from the publication, but:

mydb=# alter publication replicapublication drop table only migrations;
ERROR:  publication "replicapublication" is defined as FOR ALL TABLES
DETAIL:  Tables cannot be added to or dropped from FOR ALL TABLES publications.

I've done a huge sync of hundreds of GB, and I don't want to start over with a new publication.

Questions:
1. Is there any hope of redeeming this publication?
2. If not, is there any way to create a new publication that's fully synced without starting over with a full sync?

Thanks.


texts: Can a Join happen with same attribute names, but different domains?

texts:
Problem

When doing any sort of Join all common attributes are listed under one column in one way or another.  However, I have not been able to find a source that deal with columns of the same name, but with different domains.

Put in a visual perspective, I am taking about columns like the case below:

The domain is different because one B column is numbers but the other is letters.

Question

Is a Join possible with attributes of the same name but different domains?


texts: Resolving MySQL Process ID to page that ran it?

texts:
I asked this question on stackoverflow and was directed to ask this question here.

I have a couple of scripts in PHP that run MySQL queries.  On occasion they cause the server to seize up or become clogged with MySQL processes that take a while, for example badly formed LEFT JOINs etc.

When I run the query to find information about all current MySQL queries that have been run is it be possible to resolve a query ID to the file that ran it?

There is a query that has been sleeping for 3973 there is no info about this query and so I am trying to find as much information as possible?


texts: Upgrading to MySQL 5.6, benefits to switching to GTID replication?

texts:
We have the most basic of MySQL replication setups, a single Master doing statement-based replication to a single Slave, a 90/10 mix of innodb/myisam tables. Both run Percona MySQL 5.5 at the moment but we are upgrading to 5.6 in a couple weeks and have scheduled downtime.

Will this kind of setup see much benefit by switching to GTID replication? Do I need to switch to row-based replication as well?

Trying to decide if it is worth the extra downtime for converting to and testing GTID right now.


texts: In mysql how to identify what caused a table lock

texts:
We are using MySQL 5.6 on rhel 7. Planning to migrate to version 8.xx soon.

We have a mysql database of size 4TB. It has around 3000 tables with few tables having size of 200-300GB. Sometimes queries takes longer than 60 to 100 seconds on these bigger tables.

Sometimes due to less search conditions,  select query executes for longer time but sometimes regular queries also take long times.

It could be due to MySQL was busy doing something else .. we tried using Oracle MySQL enterprises monitor for few days. It shows long running queries but it doesn't have time stamp when a query was started. So we are not able to identify what other queries were running when one query took long time. MySQL monitor didn't report any issues too with configuration or settings..

how to identify what caused a table lock or what caused delayed response ?


texts: Generating script in SQL server taking too long

texts:
I am making backup for my tables in the database and I am generating scripts (from task) in SQL Server Management Studio.

There's about 30-40 tables that I am interested to backup including constraint,indexes, etc. I'm not doing a full backup. There's like 300 tables in the database. The entire database is so big and doing a full backup would take way too long, hence only backup certain tables.

However, in "Save or Publish script" screen, it's stuck there for very long claiming the tables are "preparing". Is there any solution to this?

Is it normal to wait that long? (1 hour)


texts:
I have the following table:

Name -   Date
Adam - 7-13-19
Stan - 7-13-19
Adam-  7-11-19
Bob -  7-10-19
Stan-  7-10-19
Eric-  7-10-19
Lucy-  7-09-19
Eric-  7-09-19
Bob -  7-09-19
Lucy -  7-08-19

I would like to pull each unique name (which I'm using GROUP BY to do) and the latest corresponding date for each name (which I'm using Max(date) to do).  However, I don't want the name to appear if the max(date) is equal to today's date (7-13-19).  So the end result I'm looking for is:

BobEricLucy

The list would not include Adam or Stan...since they both have max dates of 7-13-19.  I've tried a whole bunch of queries, but can't get it to work.  My initial thought was something like this:

SELECT *, max(date)
FROM table
WHERE max(date) <> date(now())
GROUP BY name

Any help would be greatly appreciated.


texts: FATAL ERROR: Upgrade failed when running mysql_upgrade on CentOS with SCL

texts:
We are a free/open source project. We have a CentOS 7 VM and it needed some software updated because the base CentOS 7 is stuck at PHP 5.4 and MediaWiki 1.26. We enabled Software Collections (SCL), which gains access to Apache 2.4, PHP 7.1 and MariaDB 10.2 (and friends).

We are having trouble with the MariaDB 5.5 to 10.2 upgrade which is needed for MediaWiki. We are following mariadb.com's Upgrading from MariaDB 5.5 to MariaDB 10.0. The sticking point is Step 6:

6. Run mysql_upgrade

Running the script results in:

# /opt/rh/rh-mariadb102/root/usr/bin/mysql_upgrade -u root -p
Enter password: xxxxxxxxxxxxxxxxxxxxxx
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
FATAL ERROR: Upgrade failed

We know we have the correct password for the database because we had to recover it earlier in the process. Its a simple copy/paste into the terminal so there are no typos.

We have verified the service is enabled, and its is running:

# systemctl status rh-mariadb102-mariadb.service
* rh-mariadb102-mariadb.service - MariaDB 10.2 database server
   Loaded: loaded (/usr/lib/systemd/system/rh-mariadb102-mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-12-21 14:06:26 EST; 8min ago
...

If someone could point out the problem we are having and how to fix it, we would be eternally grateful.

Thank you in advance.

This may have something to with the problem:

# ls /var/lib/mysql/
aria_log.00000001  ibdata1      ib_logfile1  mysql.sock  performance_schema
aria_log_control   ib_logfile0  mysql        my_wiki
[root@ftpit ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.2.8-MariaDB MariaDB Server
...

MariaDB [(none)]> use my_wiki;
ERROR 1049 (42000): Unknown database 'my_wiki'
MariaDB [(none)]>

However, our my.cnf tells MariaDB to use /var/lib/mysql. The [mysqld] section and data=... was added after the 5.5 to 10.2 migration. The data=... comes from mariadb.com's MariaDB Options:

# cat /etc/opt/rh/rh-mariadb102/my.cnf | grep -v '#'
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

[mysqld]
data=/var/lib/mysql

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/opt/rh/rh-mariadb102/my.cnf.d

Here are the prelude steps. They are my field notes for backtracking in case of problems. The steps consist of stopping 5.5 database, writing all data via innodb_fast_shutdown = 0, removing the old 5.5 software, and installing the new 10.2 software.

# https://mariadb.com/kb/en/library/upgrading-from-mariadb-55-to-mariadb-100/
# https://www.mediawiki.org/wiki/Manual:Running_MediaWiki_on_Red_Hat_Linux

systemctl stop mariadb.service

# Recover root password with 'mysqld_safe --skip-grant-tables...' process

yum remove mariadb

yum install rh-mariadb102

/opt/rh/rh-mariadb102/root/usr/bin/mysql_upgrade -u root -p
# The step above failed. Reboot the machine

systemctl enable rh-mariadb102-mariadb.service
systemctl start rh-mariadb102-mariadb.service

/opt/rh/rh-mariadb102/root/usr/bin/mysql_upgrade -u root -p
# The step above failed. Reboot the machine


texts: Filter not working on Showplan XML in SQL Server trace

texts:
I have setup a SQL Server trace to capture long running queries on my SQL Server 2014 box and added Showplan XML event to capture the execution plan of the queries. The trace was created with duration filter. During the testing, I found that Showplan XML didn't take duration filter into account and was showing execution plan for all queries. I was expecting only the queries with my duration filter and their execution plans in the trace. However, this was not the case. It was showing duration filter queries correctly but the execution plan was getting logged for all the queries.

Does anyone know how to get around this problem in SQL Server trace?


texts:
i have a function that convert a number to a letter.
i want to optimise this function. see query: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2586d3e0b98df1c899defc7b4078e1ef
i want to have decimal part also.
now this function just return integer part.
example:
If i have 203.145, I want to get in letter 'Two hundred three point one hundred forty-five',
Now this function just return 'Two hundred three'
Query uses:
CREATE OR REPLACE FUNCTION nombreenlettre1(
    nombre numeric)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE STRICT PARALLEL UNSAFE
AS $BODY$
/*
  Converti un nombre en lettre

  Exemple :  SELECT nombreenlettre(5423);
             cinq mille quatre cent vingt trois
 */
DECLARE
 unite text[]='{"un","deux","trois","quatre","cinq","six",
                "sept","huit","neuf","dix","onze","douze",
                "treize","quatorze","quinze","seize"}';
 dizaine text[] ='{"vingt","trente","quarante","cinquante",
                   "soixante","","quatre-vingt"}';
 coefs text[] ='{"cent","mille","million","milliard"}';
 temps text;
 c int2;
 d int2;
 u int2;
 coef int2 = 0;
 i int2;
 neg boolean;
 resultat text = '';
 n bigint;
BEGIN
  n:=nombre;

  IF n=0 THEN
   return 'zéro';
  END IF;

  neg:=n<0;
  IF neg=true THEN
    n:=-n;
  END IF;

  LOOP
   u:=n%10;
   n:=n/10;
   d:=n%10;
   n:=n/10;
   IF d in (1,7,9) THEN
     d:=d-1;
     u:=u+10;
   END IF;
   temps:='';
   IF d>1 THEN
    temps:=' '||dizaine[d-1];
    IF (d<8) and ((u=1) or (u=11)) THEN
     temps:=temps||' et';
    END IF;
   END IF;
   IF u>16 THEN
    temps:=temps||' '||unite[10];
    u:=u-10;
   END IF;
   IF u>0 THEN
    temps:=temps||' '||unite[u];
   END IF;
   IF (resultat='') and (d=8) and (u=0) THEN
    resultat:='s';
   END IF;
   resultat:=temps||resultat;
   c:=n%10;
   n:=n/10;
   IF c>0 then
    temps:='';
    IF c>1 THEN
     temps:=' '||unite[c]||temps;
    END IF;
    temps:=temps||' '||coefs[1];
    IF (resultat='') and (c>1) THEN
     resultat:='s';
    END IF;
    resultat:=temps||resultat;
   END IF;
   IF n>0 THEN
    coef:=coef+1;
    i:=n%1000;
    IF (i>1) and (coef>1) THEN
     resultat:='s'||resultat;
    END IF;
    IF i>0 THEN
     resultat:=' '||coefs[coef+1]||resultat;
    END IF;
    IF (i=1) and (coef=1) THEN
     n:=n-1;
    END IF;
   END IF;
   EXIT WHEN n=0;
  END LOOP;

  IF neg THEN
   resultat:='moins'||resultat;
  END IF;

  return resultat;
END;
$BODY$;

select nombreenlettre1(2053.234)


texts: Postgres - Ignore result of function call in select statement

texts:
I would like a way to run a function on all values returned from a query where the return value of the function is unimportant (even void in this case). A select statement may be the wrong way to do this so an alternative to using SELECT would also work.

This is what I cam up with for the time being which works but feels hacky:

SELECT *
FROM ( SELECT foo.my_function(row_name) AS ignore
       FROM foo.my_table) AS ig
WHERE ig.ignore IS NULL;

This gives me back 0 rows as I hoped for (instead of an empty row for every row in foo.my_table that the function was run on). Is there an easier way to achieve this?


texts: Why a dropped index is still there, and does it prevent a table from being altered?

texts:
I have a column (named RequestDate) which I need to change from NULL to NOT NULL, but there's a clustered index on that column, so I first need to drop that index.
Originally, the index was created using:
CREATE CLUSTERED INDEX IX_RequestDate
    ON [MyDB].[dbo].[MyTable] (RequestDate);

To drop it, I executed:
Drop Index IX_RequestDate On [MyDB].[dbo].[MyTable]

And then tried to change the column nullity using:
ALTER TABLE [MyDB].[dbo].[MyTable] ALTER COLUMN [RequestDate] DATETIME NOT NULL

But got an error:

The object 'MyTable' is dependent on column 'RequestDate'.

I then listed MyTable's indexes using this query, and the index is still there, though now it doesn't have a name and its type is HEAP (originally CLUSTERED):
TableName   IndexName   IndexType   ColumnOrdinal   ColumnName     ColumnType
MyTable        NULL       HEAP         0            RequestDate     datetime

I have 2 questions:

Why the dropped index is still there?
Did I got the dependency error above because of this still-existing index?
If so, how can I remove it?
If not, is there something else I should check? I'd like to avoid dropping the table.

Note that there are no constraints on this table.
I'm using SQL Server 2014, OS Windows Server 2012 R2, and the following is the output of SCRIPT TABLE AS -> CREATE TO (I changed some of the column names):
CREATE TABLE [dbo].[MyTable](
    [RequestDate] [datetime] NULL,
    [UserName] [nvarchar](50) NULL,
    [HostName] [nvarchar](20) NULL,
    [RequestContent] [ntext] NULL,
    [ResponseContent] [ntext] NULL,
    [RequestStatus] [int] NULL,
    [ErrorMessage] [ntext] NULL,
    [Duration] [float] NULL,
    [ServiceName] [nvarchar](100) NULL,
    [Direction] [int] NULL,
    [RequestId] [uniqueidentifier] NOT NULL,
    [IsRetry] [bit] NULL,
    [CallerId] [nvarchar](100) NULL
)


texts: I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but when I login as user1 it still shows postgres as the owner. For instance, when logged in as the postgres user and executing \dt or select * from pg_tables where tableowner = 'user1' it shows user1 as the owner, but when I login as user1 and do the same thing it still shows postgres as the table owner. I've tried restarting the service and that didn't change anything. What am I missing?


texts: Programmatically get parameters required by DACPAC

texts: Is there a way to find out what parameters a DACPAC requires? I know they are listed in an error message if you try to publish without parameters, but that takes too long - 10-15 seconds. Also, a database needs to be targeted to get the error message with the list.


texts:
I have a large number of tables that may change, may have infrequent changes in index, etc.  I may well hand this off to somebody else and I don't want indexes to become overly fragmented, slowing the system to a crawl.  So, I began hunting and found this:

http://sqlmag.com/querying/querying-sysindexes-system-table

With some changes to the "WHERE" criteria I was able to isolate my table of tables/indexes

SELECT USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) OwnerName,
       OBJECT_NAME( i.id ) [TableName],
       i.name AS [IndexName],
       CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')
             WHEN 1 THEN 'YES' ELSE 'NO' END [IsClustered],
       CASE INDEXPROPERTY(i.id, i.name, 'IsUnique')
                WHEN 1 THEN 'YES' ELSE 'NO' END [IsUnique],
      STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
FROM sysindexes i
WHERE USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) = 'dbo'
    and INDEXPROPERTY( i.id , i.name , 'IsClustered') = 1
ORDER BY OwnerName, TableName, IndexName

I know that I can loop through this result set using dynamic SQL and apply this:

ALTER INDEX Index_Name ON Table_Name REBUILD PARTITION = ALL WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

Where "Index_Name" and "Table_Name" are substituted for each time the line is run, and while this seems fine and good I can't help but wonder if there is a better solution.  Can I avoid cursors and dynamic SQL in this case?


texts: Difference between " ANY(…)" and "NOT = ANY(…)"?

texts:
Could you please explain the difference between these two operators?

sID != smth and not sID = smth.

At a first glance they seem perfectly equal. But they give different results.


texts: Mongo db and sharding / replica sets concept questions

texts:
I have few questions about sharding and replica sets:

I am planning to write important logs to a sharded environment. about 100 million rows in a day, If I will connect to a single MONGOS process , wouldn't it be over loaded? can I define more entry points to the db?
Can a config server be in the same servers as the sharded servers?
if you look at this image : http://i.stack.imgur.com/lcp4X.png , in the bottom, 2 shards nodes that is a replica set. How come a shard be a replica set? isn't replica set is a full data node and a shard is a subset of the data? how can they be together?

here is what I don't get ):

"... A replica set is a group consisting of 1 primary and N secondaries. If you are sharding, each group will be a shard. That means that the single primary and all secondaries of the same group will have the same data (replicated)..."

From my understanding:
replica set is one primary that accepts all data.
Its data getting synced to the secondaries.

Sharding is a set of instances that accept subset of the data.

So, if replica set has all the data, and a shard has some data (on each server), how can they live together?


texts: Query planner not using Index scan for WHERE IN condition

texts:
I have a table INVENTORY in PGv9.6.11 and when a run query with WHERE _column_ IN(), query planner using seq scan even have index on that _column_

CREATE TABLE INVENTORY
(
    process_id bigint,
    load_dttm timestamp(6) without time zone,
    md5_checksum character varying(200)  ,
    storeid character varying(200),
    inventorystatus character varying(200),
    inventorytype character varying(200),
    materialdescription character varying(200),
    serial_nbr character varying(200) ,
    activityid character varying(200) ,
    channel character varying(200) ,
    lastupdateddate timestamp(6) without time zone,
    src_file_date timestamp(6) without time zone,
    run_id character varying(40) "
)

I have created indexes in a different ways but none of them are using query planner ,

Reguler Index:

Create index idx_test1 on INVENTORY (inventorystatus)

partial Index:

Create index idx_test2 on INVENTORY (inventorystatus) where inventorystatus = ANY ('{Received,Returned}')

and

Create index idx_test3 on INVENTORY (inventorystatus) where inventorystatus in ('Received','Returned')

When I run explain analyze on the user query:

EXPLAIN ANALYZE select * from  INVENTORY
where INVENTORYSTATUS in ('Received','Returned')

And Query planner is:

Seq Scan on inventory  as inventory (cost=0..121384.49 rows=2603713 width=157)
Filter: ((inventory.inventorystatus)::text = ANY ('{Received,Returned}'::text[]))

Not sure what I'm missing here, any suggestions?


texts:
PostgreSQL version : 11.2
OS platform : RHEL 7.6

I am new to PostgreSQL. I have a CRM application whose data I want to store in my 11.2 PostgreSQL Database.

The Database name will be : crm_db
The schema name will be   : crm_app

I want all the schema objects in crm_app schema to be owned by a database user called crm_usr.

Can I just create a DB user using CREATE USER command like below or should I create a ROLE called crm_usr ? What is the recommended way to do this in 11.2 (the latest version at the time of posting this ie.23-Feb-2019)

CREATE user crm_usr identified by tiger235;

CREATE DATABASE crm_db OWNER crm_usr;


texts: I just started using pgAdmin3. I do not like having to have a new window when I want to run ad hoc SQL queries. Is there a way to do dock it within the top level app and actually show in the main space (to the right of the connection hierarchy view on the left)?


texts:
I recently had to deal with migrating some servers over (rather was forced to by the hosting company) and we took too long to migrate our data so they did it for us. Unfortunately they just copied /var/lib/mysql for the database. Which created all sorts of errors when trying to get the data working again. So I ran mysql in innodb_force_recovery = 1 mode and was able to take dumps of all but 1 of my databases.

Then I completely uninstalled mysql and reinstalled and tried to import the dump files into the newly created databases.

Then I got this error:

ERROR 1030 (HY000) at line 810: Got error -1 from storage engine

So line 810 is the first data insert. It seems there is not problem creating tables, but anytime I try to insert I get that error. I can't seem to find any documentation on what that error means.

How do I go about resolving this error?

Note: After a bit more digging, it seems to be an error that only occurs with innodb tables. In fact I tried converting a myisam table to innodb and got the same exact error.


texts: Unable to establish linked server connection to SSAS 2008 R2 server

texts:
I'm interested in gathering some DMV data from our SSAS cubes and store it on our management box. When I create the linked server for this effort I run into an error establishing the connection.

TITLE: Microsoft SQL Server Management Studio
------------------------------

The linked server has been created but failed a connection test. Do you want
to keep the linked server?

------------------------------
...

------------------------------

Cannot initialize the data source object of OLE DB provider "MSOLAP" for
linked server "PROD\SSAS08R2".
OLE DB provider "MSOLAP" for linked server "PROD\SSAS08R2" returned
message "XML for Analysis parser: The MSOLAP property was not recognized.".
OLE DB provider "MSOLAP" for linked server "PROD\SSAS08R2" returned
message "Errors in the OLE DB provider. An error occurred while named
instance information was being retrieved from the SQLBrowser service on the
'PROD' server.". (Microsoft SQL Server, Error: 7303)

I'm using these settings when creating the linked server.

EXEC master.dbo.sp_addlinkedserver @server = N'PROD\SSAS08R2'
, @srvproduct=N'MSOLAP'
, @provider=N'MSOLAP'
, @datasrc=N'PROD\SSAS08R2'
, @provstr=N'MSOLAP'
, @catalog=N'Prd_Analysis_Services'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PROD\SSAS08R2'
, @locallogin = NULL
, @useself = N'False'
, @rmtuser = N'WINAD\SrvcAcct'
, @rmtpassword = N'XXXXXX'

What do I need in order to get around this error and make a successful connection to SSAS? Is there a better/easier way to extract DMV data from SSAS?


texts: How to install tds_fdw on AWS RDS for SQL Server

texts:
I am using AWS RDS Posgres DB. I would like to connect Postgres to my SQL Server 2019, so that I can have sql server tables available in foreign table drop down of postgres for querying. As I know, tds_fdw data wrapper is required. When I tried to create tds_fdw extension through super user account in AWS RDS, I got the following error:
ERROR:  Extension "tds_fdw" is not supported by Amazon RDS
DETAIL:  Installing the extension "tds_fdw" failed, because it is not on the list of extensions supported by Amazon RDS.
HINT:  Amazon RDS allows users with rds_superuser role to install supported extensions. See: SHOW rds.extensions;
SQL state: 22023
Is there any way to install tds_fdw on AWS RDS? If not then, what other ways I can use to connect AWS RDS Postgres to my SQL Server?


texts: Query does not use index when selecting all records

texts:
This is my first time handling a large database, and I was wondering why the two queries below are different in terms of execution plan.

I have setup an index on a specific table:

CREATE NONCLUSTERED INDEX [IX_UBHSD_xploc_trandt2] ON [dbo].[UBHSD]
(
    [xploc] ASC, [trandt2] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
        DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
        ON [PRIMARY]
GO

When I ran this query, it uses the index.

SELECT COUNT(*) as cnt
FROM [dbo].[UBHSD]
WHERE [xploc] != '' AND [trandt2] >= CAST('2012-01-01' AS DATETIME)

Here's a larger one.

But when I ran a query without using aggregate:

SELECT *
FROM [dbo].[UBHSD]
WHERE [xploc] != '' AND [trandt2] >= CAST('2008-01-01' AS DATETIME)

Here's a larger one.

I was expecting that both queries would use an index. But the second query performs a full table scan.

Why is this happening? Did I miss something?


texts: MongoDB Replica Set Sync data by Copying Data Files from Another Member

texts:
I used MongoDB 3.6 and was following MongoDB documentation:

https://docs.mongodb.com/manual/tutorial/resync-replica-set-member

to do a full copy of data from Primary node to another mongod instance. I copied both admin and local plus all database folders into the new node. After all data are ready, added new node into replica set. It seems the replica set starts another initial sync and copy data from Primary to the new node. I got 400 GB data in primary node, so after a few hours Secondary node data size reach 600 GB and no disk space left and cause the sync fail. So it seems MongoDB still copying all data from Primary to Secondary even after I did this data copy manually. More surprisingly, it keeps the data I copied and continue to do its own data sync. Any suggestion?


texts: Spooling Oracle XML data results in 1 line and missing data?

texts:
I am trying to spool Oracle XML data to a file but the resulting file ends up with 1 line which is stops after a certain amount of characters so doesn't contain all of the data?

set colsep ,
set feedback off
set headsep off
set linesize 32767
set pagesize 0
set trimspool on

spool $FILE

select
  xmlelement("apiRequest",
    xmlelement("orders",
      xmlagg(
        xmlelement("order",
          xmlelement("no", orders.order_no),
          xmlelement("date", orders.date),
          xmlelement("orderItems",
            xmlagg(
              xmlelement("orderItem",
                xmlelement("position", order_items.item_position)
              )
            )
          )
        )
      )
    )
  )
  as xml
from
  ...


texts:
Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and which columns should i use in incluse list.
I am using SQL Server 2008/R2


texts: How to group many information une one select

texts:
There are my database

articles
id | art_code | art_name
1  | 5678     | computer
2  | 0987     | display
3  | 8547     | keyboard

action
id | code        | action      | qte
1  | 5678        | IN          | 4
2  | 0987        | OUT         | 3
3  | 5678        | IN          | 6
4  | 5678        | OUT         | 2

I would like to SELECT all value of article with SUM() of IN / OUT group by article

ex:

RESULT:

    id | art_code | art_name  | IN  |  OUT
    1  | 5678     | computer  | 10  |   2
    2  | 0987     | display   | 3   |   NULL
    3  | 8547     | keyboard  | NULL|   NULL

I try but, I've a result with :

RESULT:

    id | art_code | art_name  | IN  |  OUT
    1  | 5678     | computer  | 10  |   NULL
    1  | 5678     | computer  | NULL|   2
    2  | 0987     | display   | 3   |   NULL

I can't have SUM(IN) and SUM(OUT) in 1 line by article.art_code.

An idea?


texts: Is it possible to deny RESTORE permissions to login with db_owner role membership?

texts:
Is it possible to deny RESTORE permissions to a login with db_owner role membership?

Is dbcreator required to RESTORE over an existing DB with a BACKUP of that DB? Or is that only needed when creating a new DB from a BACKUP?

I'm trying to prevent a user with db_owner membership from accidentally restoring to the wrong server/instance.


texts: BCP via Linux Centos 7, cannot connect to Azure SQL

texts:
I installed sqlcmd, bcp and odbc 17 for microsoft.

then I connected to Azure SQL with SQLCMD - it worked OK

then I tried to import some data with BCP and i got an error

I use:

 bcp schema.table_name in "path to csv file" -S example.database.windows.net -U user_name -P "password" -d schema -c -t ','

SQLState = 28000, NativeError = 18456
Error = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'user_name'.
SQLState = 01S00, NativeError = 0
Warning = [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute

My passwords contaains ] % ; , is it could be a problem?

Update:

The issue is that when I use SQLCMD unixODBC uses {} to escape password and it works OK, but when BCP uses unixODBC password is not escaped.

SQL CMD

[ODBC][12517][1530961370.439037][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x1b56e20
            Window Hdl = (nil)
            Str In = [DRIVER={ODBC Driver 17 for SQL Server};SERVER={example.database.windows.net};UID={user_name};PWD={password};WSID={ADCSrv...][length = 145 (SQL_NTS)]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = (nil)
            Completion = 0
        UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

BCP

[ODBC][19984][1530962482.429621][SQLDriverConnect.c][726]
        Entry:
            Connection = 0xcb4ef0
            Window Hdl = (nil)
            Str In = [DRIVER={ODBC Driver 17 for SQL Server};server=example.database.windows.net;UID=user_name;PWD=password][length = 109 (SQL_NTS)]
            Str Out = 0x7fff5397ce70
            Str Out Max = 2048
            Str Out Ptr = (nil)
            Completion = 0
        UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

How to fix that? I think about creating a DNS


texts:
I was just wondering, what would be the table structure for a messaging system similar to Gmail's priority inbox?
If you're unfamiliar with the system, it prioritises messages based on the type of messages you most often read and those to which you reply, then separates them from 'normal' messages.
In this hypothetical scenario, I'm basing the 'score' only on senders to which the user replies.

I was thinking that each user would have a table assigned to them that recorded the user ids of the top 10 people they responded to, each user name requiring 5 replies to be entered into said table. For each reply, the 'replied' value would increment by one.
Is that the best/most logical way of doing it?

I'm not familiar with 'NoSQL' databases, so my logic is based on a relational methodology. My apologies if I'm missing something obvious because of that.


texts: Extract from DB2 to SQL Server - where is the data in between the two servers?

texts:
I am trying to understand what happens when I extract data from DB2 from a SSIS package and load it into SQL Server.
My question is: where is the data when it is between DB2 and SQL Server? On my personal computer? I mean does the data "pass through" the memory on my personal laptop and then gets sent to SQL Server?


texts: If a database is in memory does it still need indexing?

texts:
INFO I'm following lessons on databases. We're learning about indices now.

Question

If big parts of a table are in the memory, would using a index still have benefits?

My side

I would assume indexing makes a search always faster, so yes it would have benefits. I hope this is not to much opinion based and maybe someone have a good explained reason why or why not it has benefits.


texts: SQL Server 2016: Bidirectional Transactional Replication using Backup, Do I use/need Snapshot?

texts:
Overview:

I have been setting up bidirectional transactional replication between 2 SQL Servers. We have another pair of servers, running Inductive Automation's Ignition Gateway server creating and sending SQL data to Server 1 as the primary data source, and Server 2 as a backup data source. There are 3 databases that I need to keep synchronized at all times on each of the servers, so if a failover occurs the backup has the same data the primary has.

I have been setting up and testing this configuration for a long time, and testing the failover functionality of the Gateway software. All seems to be working very well, and the databases seem to be synchronizing.

The instructions I used had me setup network shares for the snapshots on each server, but since I used a backup of the 3 databases, it doesn't appear to ever use the Snapshots.

One of these synchronized databases is over 600GB of data. The deployment took 3 steps over a weekend.

Implementation:

Step 1: was starting the SQL Server Maintenance Plan of the primary server, consolidating\backing up all databases. This took over 6 hours to complete.

Step 2: was copying the files to the Backup Server, and restoring the databases to the Backup server, again taking many hours to finish.

At this point all 3 databases on Server 1 and Server 2 are identical.

Step 3: I ran my scripts that: activate Distribution on each server, Publish the 3 databases on each server, and subscribe each server to the published databases on the other server (ensuring that loopback detection was set to true for each subscription).

Questions:

What are the snapshot folders for, and why were they needed to setup the synchronization? I suspect something is either not working, or the part of the instructions that had me setup the network shares "\Server1\snapshots" and "\Server2\snapshots", was completely erroneous.
What is the Snapshot Agent supposed to be doing for the type of synchronization we have here?
I am seeing options in SQL Server Management Studio under the Replication section to 'validate', 'reinitialize', and 'check subscription status' of the subscriptions.  I've run each and the results don't give me the expected feedback. What is the difference?
In the event that the subscriptions have problems, the only maintenance procedure I feel confident about is repeating the steps we used to deploy this configuration.  The length of time it takes to complete this leaves me very nervous!  Production runs: Mon-Wed 24 hours, Thurs-Sat 4AM-4PM.  Interruptions to production are very expensive.  I'm looking for any guidance you might have about maintaining this setup.
Day 1 after implementation, I spotted that the Snapshot Agents had never started in the Replication Monitor, so I started the Agents manually.  When I did, both servers became unresponsive and had to be rebooted.  Now the Log Reader agents all look happy, performance is all excellent.  However, the Snapshot agents all have errors "Not completed", and I cannot seem to reset them or clear them in the Replication Monitor. How do I fix this?


texts: Data Sync between servers with different schema in SQL Server

texts:
I have two servers 2008 R2 and 2005. The e-commerce site has been running with 2005 Server. I have migrated 2005 server data with modified data schema to 2008 R2 from yesterday's date of data. My client wants to migrate the complete DB to 2005 R2 whenever he will gets the new site from developers but I need to update changes from 2005 to 2008 R2 with modified schema everyday.

How can I sync the data having different data schemas of different servers?


texts: ALTER primary key column from INT to BIGINT in production (MySQL 5.6.19a)

texts:
Some INNODB tables in our production database are about to hit the INT AUTO_INCREMENT limit of 2147483647 and we need to alter them to BIGINT otherwise writes will start failing.

The tables are in a production MySQL 5.6.19a database running on Amazon RDS.

How can we do an ALTER like this without disrupting the production reads and inserts that are happening all the time?

ALTER TABLE MYTABLE CHANGE id id BIGINT NOT NULL AUTO_INCREMENT;

Here is DDL for the table:

CREATE TABLE `MYTABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteId` int(11) NOT NULL,
  `filter` varchar(10) NOT NULL DEFAULT 'ALL',
  `date` varchar(10) NOT NULL,
  `cards` varchar(250) NOT NULL,
  `apples` varchar(45) NOT NULL,
  `carrots` varchar(45) NOT NULL,
  `corn` varchar(45) NOT NULL,
  `peas` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`siteId`,`filter`,`date`,`cards`),
  KEY `date_k` (`date`),
  KEY `cards_k` (`cards`),
  KEY `apples_k` (`apples`),
  KEY `siteId_k` (`siteId`)
) ENGINE=InnoDB AUTO_INCREMENT=1748961482 DEFAULT CHARSET=utf8


texts: Can I retrieve all database objects owned by a particular database?

texts:
Is there a way to search all database objects by a particular database name or its id. Is that possible? I would like to do this so that I can find out how many stored procedures, functions, views, etc on each DB. By googling, I found some posts about searching objects by user but not database.

Update:
To make my question little bit more clear, I already have this code to get total count of each object type for a specific DB.

SELECT
    SUM(CASE WHEN CHARINDEX('FUNCTION', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions],
    SUM(CASE WHEN CHARINDEX('STORED_PROCEDURE', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs],
    SUM(CASE WHEN CHARINDEX('TRIGGER', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers],
    SUM(CASE WHEN CHARINDEX('VIEW', o.type_desc) > 0 THEN 1 ELSE 0 END) [views]
FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id

But I was wondering if I can iterate through sys.databases and return result like below:


texts:
I have setup Master-Master replication on two servers. Now, my question is that if I need to have the second master as only read-only, what should I do? I tried doing

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> set global read_only=ON;
Query OK, 0 rows affected (0.00 sec)

But since this creates a read-lock in my 2nd master (Slave 1). The changes that I make in Master 1 is not propagated to Master 2 (Read-only).

What should I do such that - changes in Master 1 are getting updated to Master 2 but Master 2 should not be in write mode, Master-master(Active/Passive).
Is it possible to do this?

Regds


texts:
I have been trying to install SQL Server and now I am tired of it. I tried to version SQL Server 2008 and SQL Server 2008 R2. SQL Server 2008 gives the following error (.Net Framework installed)

  Application requires .Net Framework v4.0.30319 AND invoke or BeginInvoke cannot be called until the window handle has been created

SQL Server 2008 R2 was giving the following error:

  The following error has occurred:

  An error occurred during the installation of assembly 'Microsoft.SqlServer.GridControl,fileVersion="10.50.1600.1",version="10.0.0.00000",culture="neutral",publicKeyToken="89845DCD8080CC91",processorArchitecture="x86"'. Please refer to Help and Support for more information. HRESULT: 0x8002802F.

and after that a new error

  The following error has occurred:

  An error occurred during the installation of assembly 'Microsoft.VC80.CRT,version="8.0.50727.4027",publicKeyToken="1fc8b3b9a1e18e3b",processorArchitecture="x86",type="win32"'. Please refer to Help and Support for more information. HRESULT: 0x80070002.

EDIT:

Where in the world can I find .Net Framework v4.0.30319? Its so annoying and confusing.


texts: Can I minimize maintenance of duplicated Windows + SQL Auth logins?

texts:
In our environment, SQL authentication is the norm, but Windows auth is possible when users are using RDP.  As a result, each physical user has two logical logins for each instance and two logical users for each database on each instance.

Is there any way to link two logins, so I can (e.g.) grant privs to both simultaneously?

This is a small thing, but seeing repetition like this triggers my "there's got to be a better way" sense:

CREATE LOGIN Bob
CREATE LOGIN Domain\Bob
ALTER SERVER ROLE serveradmin ADD MEMBER Bob
ALTER SERVER ROLE serveradmin ADD MEMBER Domain\Bob
USE Db;  CREATE USER Bob FOR LOGIN Bob
USE Db;  CREATE USER Domain\Bob FOR LOGIN Domain\Bob

Am I overlooking a feature that would reduce this duplication?

We'll eventually replace domain logins with AD groups, which will help.


texts:
Is there any risk in DROPPING users in postgres?
We have bunch of users in our database that no longer work at the company.
I was wondering what (if any) risk there was in dropping those users?


texts: SQL Server does not exist or access denied from a VMWare guest

texts:
I've made the configuration to allow remote connections to SQL Server 2012, however I still cannot connect to it from a VMware Workstation Guest (using both NAT and Bridge).

I can ping the host machine from the guest VM when using NAT (the same is not true for bridge config).


texts: Count percentage of column with GROUP BY

texts:
Consider the following query:

SELECT
  country
  , count(id) AS "count"
FROM
  lead
WHERE
  date_part('year', lead.since) = date_part('year', CURRENT_DATE)
GROUP BY "country"
ORDER BY "count" DESC
;

It returns something like:

country count
fr  3456
us  569
sc  248
…

How can I add a third column with the percentage of the total count?


texts: Do I need to install SQL Server service packs to continue receiving security patches

texts:
It's always been my belief that SQL Server needs to be updated to the latest service pack in a timely (and tested) manner to continue receiving security/bug patches.

I'm now partially responsible for a 2008 R2 DB that has not had any service packs installed. The external IT company mainly responsible for maintaining it has told me that they don't install service packs unless there is a specific business need, and that it would automatically recieve security patches any way. Is this the case, or do you need to keep up-to-date (once tested) with the latest service pack?


texts:
I've got a Twitter database and some columns in multiple tables are time-stamps like:

Tue Sep 09 23:48:37 +0000 2014

These columns are TEXT so I want to convert them. I read
Date and Time Types,
DATE_FORMAT, and STR_TO_DATE. It seems like that there's no symbol to recognize the timezone pattern, and I can't ignore it because it's not at the end of the string therefore STR_TO_DATE can't leave it there.

Is there some function or walk-around to deal with this problem? Thanks a lot!


texts: How do I deal with the inevitable scenario when a table's bigserial runs out of ids but many of the old records are deleted?

texts:
I use PostgreSQL. I have many database tables which contain data of the kind that is regularly "replaced entirely", as in, I first clear (delete) all existing records, then just insert all-new records.

Previously, I did this with:

DELETE FROM the_table;

However, this kept increasing the serial column (id) all the time, eventually leading to it reaching the maximum number. I'm still unsure what happens at that point; I hope that it just resets automatically, but I fear that it probably makes the table stop working and start spitting out errors whenever new records are attempted to be INSERTed.

To solve this for these tables, I have started doing this instead:

TRUNCATE TABLE the_table RESTART IDENTITY[ CASCADE];

This does the same thing as DELETE FROM, but also resets the serial column. Great. Exactly what I wanted.

However, I also have many tables where the data needs to be preserved, and cannot be cleared like the ones I described above. I obviously cannot run TRUNCATE TABLE on those tables, but they will also eventually reach a point where either the serial or bigserial maximum is reached.

It doesn't matter if this "probably will take years". Just knowing that this will/might one day happen is enough to give me constant stress. Is there something I can do to avoid that scenario? Does PG handle this automatically? Can it be told somehow how to behave when this inevitably happens?

Since many old records likely will have been deleted by that point, I would like it to start using those unused ids automatically. For example, if ids 0-1000 are empty when it reaches the maximum serial value, instead of failing, it would start using 0, 1, 2, 3, 4... until it gets to 1000 and then it looks ahead for any unused ids until it finds one.

If the above idea is stupid, I want to hear what you all do about this problem.


texts: How do I retrieve the loop record in the connect by clause?

texts:
I have a employee-->Manager hierarchy and using the following query:

SELECT  EMP_ID, FIRST_NAME CONNECT_BY_ISCYCLE "CYCLE" FROM  MYTABLE CONNECT by NOCYCLE PRIOR MANAGER_EMP_ID = EMP_ID START WITH EMP_ID = '12345'

We are getting the following as the output

12345   Nan 0
A12345  John 0
B12345  Dumble 0
C12345 Mark 0
D12345 Shady 0
E12345 Yenn 1

The problem here is because the CEO (F12345) is his owner manager, a LOOP occurs and hence , I am not getting the CEO record at the end of the table.  My expected output is:

12345   Nan 0
A12345  John 0
B12345  Dumble 0
C12345 Mark 0
D12345 Shady 0
E12345 Yenn 1
*F1345 Don*

Is there a way I can retrieve the LOOP record too? If there is no other way, I have to probably do two query and fetch the CEO is the second one.

Any guidance is appreciated.


texts: Mysql Order By affecting a Query with column alias, group by, and having

texts:
Description:

I have a query with column alias, group by and having. It's getting altered when I add an order by.

This strange behavior is only happening in 5.6.16 and 5.6.21 (which I was using in development, over Windows)

It was not happening on 5.5.41 and 5.5.43 (which I had in production, over Linux)
So I downgraded my local MySQL to 5.5.* and the query worked as expected.

How to repeat:

I'm not sure on how to repeat, but I will do my best in order to show this strange behavior

My query:

SELECT
a.*
, if(u.status = 1 AND a.acre_aprobado = 0, 5, u.status) tipo
FROM acreditado a
JOIN users u ON (`a`.`acre_id` = `u`.`id`)
WHERE `acre_test` = 0
GROUP BY `a`.`acre_id`
HAVING tipo = '1'
#ORDER BY `acre_aprobado` ASC

The result in both 5.5.* and 5.6.*: (without the order by)

============================================================
acre_id | obac_id | ... | tipo
27      | 23      | ... | 1
37      | 22      | ... | 1
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

Now the result when I add order by:

5.5.*

============================================================
acre_id | obac_id | ... | tipo
27      | 23      | ... | 1
37      | 22      | ... | 1
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

5.6.*

============================================================
acre_id | obac_id | ... | tipo
25      | 24      | ... | 0
32      | 24      | ... | 0
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

As you can see, in 5.5.* everything works as expected, but in 5.6.* order by is modifying the result, and it's even making "having" don't filter

I created an SQL fiddle

http://sqlfiddle.com/#!9/c928c/8

First run the query as is, and then uncomment "order by" and run it.

I read something about MySQL Extensions to GROUP BY from the post
Why does MySQL allow HAVING to use SELECT aliases? that might have some to do with this issue, but I wonder why the different behavior between 5.5.* and 5.6.*


texts: What is the last Mysql Version supported in REDHAT 6?

texts:
Maybe not such a good question ! But my Linux admin is to busy to answer my doubts. SO:

I have all my Mysql servers running on 5.1.69 version(the free one) that comes with RedHat O.S. My users are forced to upgrade their Mysql Versions because of the Drupal and moodle needs !!
  So i need to know if RedHat goes beyond  5.1.69 version or not. If not i will install a new version with no support or buy Oralcle support (which is not an option i think for my users as they are so cheap kkkkkkkkk)
THX