texts: Prevent reverse engineering of encrypted views

texts:
I have an encrypted view definition in SQL Server that has some information that I want to protect. The definition looks something like this:

CREATE VIEW myview WITH ENCRYPTION AS
SELECT name, age FROM customer WHERE c_city = 'NY'

Now I don't want to expose the fact that the view has a predicate on c_city with the value 'NY'. But when I run the query and see the execution plan for the following query:

SELECT * FROM myview

I can see the predicate on the underlying table with the constant value as well. So anybody can reverse engineer my view logic this way. How can I prevent this?


texts:
We have this problem in our production environment.

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) - Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1).

SQL Server is dropping all (almost 100% of) the old execution plans and recreating them everyday overnight (from 11:00 PM to 8:00 AM). This was even happening when the 'auto update stats' was at disabled state. We've turned the 'auto update stats' on for the last 2-3 weeks. But it's still happening.

We don’t really know what triggers this re-generation of plans but we are sure we don’t do it manually.

The only thing that really coincides with the timing of the plans being regenerated is a DB maintenance job that we have: the daily index reorganizing (when fragmentation is 5-30%), and the daily index rebuilding (when fragmentation is more than 30%) job. Usually this daily maintenance job only does reorganizing (as the index fragmentation is never more than 30% on daily basis).

Impact:

These newly created plans make some UDF calls/query calls (that are called from UI/web pages) take way longer (minutes as opposed to less than 1 second), and so the sessions just get piled up taking the CPU close to 90%.

The problem goes away the moment those stuck sessions are forcefully deleted (on the DB side), and 1) when all the corresponding execution plans are cleared manually (for queries) or 2) when the UDFs are altered (for functions). Any new plans created by SQL server from that moment work just perfect throughout the day until it ends up having the same issue next morning. Also, this behavior is not 100% consistent, we're not really seeing it each and every morning. But there have been periods of time where we've been seeing it consistently for 4-5 days in a row.

The problem happens on business mornings, that's when UI/web pages are accessed more intensively, it seems.

Does anyone have a clue what is causing this and how to solve this problem? Any help would be much appreciated.


texts: Security: direct remote access vs webinterface

texts:
I need to remotely connect to my (mongoDB) database and I currently allow direct access to the DB server port with user/pw login.

I often read that this is generally a bad idea and that one should rather limit the DB to localhost and use a webinterface (e.g. phpmyadmin, or mongo-express in my case) to allow remote data access.

However, I do not fully understand the advantage of this. Both access methods will have the same permissions for the corresponding user accounts. So an intruder would be able to perform the same operations.


texts: How to Convert Data to Proper Case In SQL Server

texts:
SQL Server contains system functions for viewing / updating string data to both uppercase and lowercase but not proper case. There are multiple reasons for desiring this operation takes place in SQL Server rather than at the application layer. In my case, we were performing some data cleanup during a consolidation of our global HR data from multiple sources.

If you search the internet you will find multiple solutions to this task but many seem to have restrictive caveats or do not allow for exceptions to be defined in the function.

NOTE: As mentioned in the comments below SQL Server is NOT the ideal place to perform this conversion. Other methods were suggested as well - such as CLR, for example. This post has served it's purpose, in my opinion - it's great to have all of these thoughts in one place as opposed to the random tidbits available here and there. Thank you to all.


texts: mysql 5.7.9-log set session mode gives warning about NO_AUTO_CREATE_USER is deprecated

texts:
When changing the session mode in mysql 5.7 I get the following warning.

"(Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release."

I don't exactly understand what it means. Is mysql removing the ability to be able to set a session mode?

Mysql terminal

mysql> \W
Show warnings enabled.
mysql> SET SESSION sql_mode="NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
mysql>


texts: Automatically replicate table data into another table

texts: Is there a way to automatically replicate the table into another table where in if table A has changes, then it will also change the data in the 2nd table.


texts: Possible to drop table ignoring dependencies?

texts:
I had a table from which I wanted to delete a lot of rows but that was failing because of memory constraints.  I saw a suggestion to "simply" copy all the rows that I don't want to delete into a new table, drop the old table, then rename the new table to the old table's name.  That would be fine except for other objects that depend on the original table.  From what I can tell that means I'd have to drop cascade and then recreate everything that depends on the original table.

Is there a way to either script everything that depends on the original table so I can easily recreate them in pgadmin or psql?

Alternatively, is there a way to ignore the dependency long enough to delete the old table and rename the new table to the old table's name?

I believe this is how pg_repack works so there must be a way.


texts: DB2 BOOLEAN type, EXISTS predicate and nullability strange behavior

texts:
I'm using DB2 LUW v11.1.4.5,
It seems that EXISTS predicate, when FALSE returns NULL (is it documented ?)
In the following query on X is null is true, all the rest acts like FALSE is a synonym for NULL, but the metadata say all the columns are not nullable
(see dbfiddle)
with boolstmt as
(
  select  'exists' stmt,
    exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') as x
  from sysibm.sysdummy1
 )
select
 stmt,
 x,
 x is null "x is null",
 not x "NOT x",
 x = FALSE "x = FALSE",
 smallint(x) "smallint(x)",
 char(x) "char(x)"
from boolstmt

now with the following query every column is nullable, it shows that x really is NULL with EXISTS
with boolstmt as
(
  select  'exists' stmt,
    exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') as x
  from sysibm.sysdummy1
  union all
  select  'count' stmt,
    (select count(*) from sysibm.sysdummy1 where ibmreqd = 'N') > 0 as x
  from sysibm.sysdummy1
 )
select
 stmt,
 x,
 x is null "x is null",
 not x "NOT x",
 x = FALSE "x = FALSE",
 smallint(x) "smallint(x)",
 char(x) "char(x)"
from boolstmt

My question is : did someone know about it, it seems to me that's a problem on the server but do you know if it may come from the JDBC driver (using 4.27.25) ? Is the problem still there using later versions ?


texts: Querying an array of strings with a string that represents search terms

texts:
I have an array of values like:

array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most

and some text like: 'foo hello-world hello1world'

and I would like to get back from the original array the values foo and hello world filtering out bar.

Is there any way to get that with Postgres?


texts:
Consider this table in mysql

create table numbers (number int);
insert into numbers values (3), (2), (9);
select * from numbers;

+--------+
| number |
+--------+
|      3 |
|      2 |
|      9 |
+--------+

Is there a simple query to generate a table with the following columns

The numbers from 1 till 10
1 if the number is present in the table numbers, and 0 otherwise

I guess you have to create a sequence of numbers in order to do this. If possible, I want to create such a sequence without storing it in the database.

Related question: is there a select query that generates a sequence of numbers from 1 till 10 (or 100 or 1000)?


texts: 2NF decomposition + database normalization

texts:
If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't been able to find a really good example online. I am studying for a test and would like to know if my reasoning on how to accomplish a 2NF decomposition is correct

R = {a, b, c, d, e, f, g}
F = {AB --> C, A --> DE, B --> F, F --> GH, D --> IJ }

The first thing I did was to find the super key which was easy to see (AB)+ = R, However I am not sure if this is what 2NF definition means when it uses the term "key"

2nd I used A.A. and combined some terms in F (just to make it more managable)

F = {AB --> C, A --> DEIJ, B --> FGH}

3rd I remove and partial functional dependencies I am not really sure ( I did look it up to no eval) what a PFD is I think its when you have a FD where the LHS is a proper subset of the superkey in this case

A --> DEIJ and B --> FGH

4th I break it into relations that remove the violations from step 3

R1 = AB --> C

R2 = A --> DEIJ

R3 = B --> FGH

I'm really looking for someone to give me feedback on if I am understanding the concept correctly any help would be greatly appreciated

Thanks


texts: Reporting Services Database on different version

texts:
Just wondering if anyone has come across this scenario and can provide some advice.

We have a vendor application that only runs on SQL Server Reporting Services 2014. However, all of our other applications support SQL Server 2017.

Can I setup SSRS 2014 on a separate server, but have the database residing on the SQL Server 2017 server?

Cheers
Phil


texts:
I've got a VMWare VM that runs Windows Server 2012 R2, SQL Server 2014 Standard Edition, IIS (hosting custom web application), and SentryOne Monitoring. The VM is configured with 4 cores and 128GB of RAM, which is the maximum for the entry license for Standard Edition.

If I give the VM more cores and more RAM, will SQL Server try to use them, or will it ignore them?

This is my demo box for my company, I want to give the VM extra resources so the Web Application (IIS), and SentryOne can function quickly and reliably.  But I don't want to violate licensing.

I know I can restrict SQL's Memory usage, but what about CPU usage?


texts: SQL Server 2008, SOAP Endpoint, http.sys, timeout

texts:
I am debugging a SOAP webservice hosted by SQL Server 2008. I know that those are deprecated in future versions and we should consider a different technology, of course.
There is an exposed procedure which legitimately runs for quite a long time sometimes, depending on circumstances. Now, when it spills over 120 seconds, the connection gets reset due to a timeout, in the http error log there is an entry

443 HTTP/1.1 POST /APIDevelop/ - - Timer_EntityBody -

and the CURL-based library on the clientside gets a 104 error (CONNRESET).
We have tried to adjust the timeout settings for http.sys by running

netsh http add timeout timeouttype=idleconnectiontimeout value=480

but it seems that http.sys is not impressed by these settings (which end up in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\HTTP\Parameters), not after restarting SQL Server and eventually the whole server, either. The timeout is still there at 120 seconds. Actually, the call takes around 130s but I attribute the difference (maybe, mistakenly) to other latencies.

Anyway, the question is: SQL Server is using http.sys for running its SOAP endpoints - how can I configure http.sys's in this case? Specifically for timeouts?


texts: SQL Native Client and network disconnects

texts:
Does the SQL Native Client provide support for maintaining a persistent connection over the network at the driver level if there is a momentary network disconnect like a 999 error? Our applications are written in PowerBuilder (versions 12.5 and 6.5) and support SQL Server 2005 - 2014 and Express Edition - Enterprise Edition.

I read about the KeepAlive property at http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546852.aspx , https://technet.microsoft.com/en-us/library/ms190771(v=sql.120).aspx , and http://www.databaseskill.com/2357386/ . Would this help with this situation?

I also looked at the Microsoft site https://msdn.microsoft.com/en-us/library/ms130822(v=sql.120).aspx which lists all the SQL Native Client connection string properties. I found Failover_Partner, but this is for database mirroring, and I found MultiSubnetFailover, but this is for Always On Availability Groups (AG).

I also looked at http://www.connectionstrings.com/sql-server/.

Thank you!
Mike Petrak


texts: Help on understanding how _id works for Mongoose

texts:
So I've got a schema for a record  that is shown below that will record three different types of events:
(an example of one record that would be created that would be a segment in the test log) (log just holds an array of objects)

{
                        date_started: {type: Date, default: Date.now},
                        date_ended: null,
                        _id: //idk,
                        type: 'test',
                        log: [],
}

When one is created it receives its specific type (of which there are 3). How would I set up unique serial ids for each type? Would I need to use individual schemas?
Trying to explain in a different way:
I've got three logs to keep that all take identical inputs. How would I keep the serial number separate if I used _id? I'm rather new to MongoDB and any direction you can point me in would help. Thank you!


texts: Bad execution plan after stats update due to temp table

texts:
A stored procedure query sometimes gets a bad plan after stats update on one of the tables, but can be recompiled to the good plan right after. Same compiled parameters.

Issue seems to come from a small temp table created in the SP and then joined to. The bad plan has a warning on the temp table that the join column has no stats. What gives?

SQL Server 2016 SP1 CU4, with 2014 compatibility level

Bad plan:

Good plan:

Stored procedure

USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [MySchema].[MySP]
    @MyId VARCHAR(50),
    @Months INT
AS
BEGIN

    SET NOCOUNT ON

    SELECT *
    INTO #MyTemp
    FROM AppDB.MySchema.View_Feeder vf WITH (NOLOCK)
    WHERE vf.MyId = @MyId AND vf.Status IS NOT NULL

    SELECT wd.Col1
         , vp.Col2
         , vp.Col3
    FROM AppDB.MySchema.View_VP vp WITH (FORCESEEK)
    INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
    WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

END

Inner view

USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW [MySchema].[View_VP]
AS

    SELECT pp.Col1,
           pd.Col2 AS Col2,
           MAX(pp.Col4) AS Col3
    FROM P_DB..LargeTable pp WITH (NOLOCK)
    INNER JOIN P_DB..SmallTable pd WITH (NOLOCK) ON pp.P_Id = pd.P_Id
    WHERE pp.[Status] IN (3, 4)
    GROUP BY pp.Col1, pd.Col2

Plans

Redacted good plan and bad plan.

Additional information

The FORCESEEK hint was added at the time to try to handle this very same issue and stabilize the plan. And anyway, with or without it, I'd really like to understand what's happening here.

I'm not able to reproduce the issue at will, so it's hard to say if replacing the SELECT INTO with an explicit table would make a difference. However, I believe it should behave the same way.

SELECT
    database_id,
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE
    database_id IN (2, <relevant user databases>)

returns:

  database_id   is_auto_create_stats_on   is_auto_update_stats_on   is_auto_update_stats_async_on
 ------------- ------------------------- ------------------------- -------------------------------
  2             1                         1                         0
  7             1                         1                         1
  37            1                         1                         1

It's clear that this seek is terrible, but the question is why it doesn't do the good seek in the first place.

The query doesn't return 1m rows, the estimates are wrong. There could be slight changes in the output, but the number of rows is always pretty low (maybe hundreds at most).

Even the ones returning relatively many rows generate plans seeking by the Id and never by the status (which is not selective as you can see). I can't seem to reproduce the status seeking plan, no matter what values are compiled. I even tried to add a waitfor delay between the creation of the temp table and the second query, and update stats / recompile in a second session, with no effect either.


texts: PostgreSQL db volume at 100%. Can I create a tablespace on another volume so I can vacuum full?

texts:
I have run out of space, because I am new to Postgres and I have just not been monitoring it properly.

Now I cannot even vacuum the tables:

ERROR:  could not extend file "base/20851/11787": No space left on device

I have deleted a whole bunch of data. I realize that only vacuum full will bring back space though.

I have another logical volume with lots of space on it. Can I create some kind of a tablespace over there? If so, is there a specific kind of space to tell the database to do it's "work" over there during the vacuum process? Or do I need to physically move tables to that tablespace? How do I do that?

I apologize for the rapid fire questions. I have found myself in a bit of a spot.

This is PostgreSQL 9.3.


texts:
I want to implement a database security on the database i created so no one can access it from SQL Server Management Studio, and that it will be only accessible through code.

Is that possible? and if it's not is there any other ways to prevent user from accessing database?


texts: Create a form solely for inputting data?

texts: I am looking to create a form for a database in MS Access, that will be used solely to allow users to input data into the database. However, all the options I have found do not allow for this, they all provide navigation buttons that allow users to navigate to data that has already been entered into the forms that are related to them. How can I create a form to just allow users to input data without allowing them to look over more of the information held in the form's related table?


texts: MySQL - terrible performance for simple query on large table

texts:
I'm having huge troubles with the performance of a very simple indexed query on a quite large table.
Configuration:

48 cores, NVMe Raid, 376GB RAM (144GB used)
Mysql 8 (Percona server)
Server dedicated to DB
Application with low concurrent users
InnoDB
Overall database size: ~1TB
Table size: ~35GB
Table structure:
primary_key (int),
indexed_field (int),
value (int),
plus a few other ints, dates and a varchar

The query: SELECT AVG(value) WHERE indexed_field=[somevalue] => takes 220s for ~2M rows
Here is the associated explain

I tried the following other queries to try and understand what was going on:

SELECT COUNT(*) WHERE indexed_field=[somevalue] => 1s (~2M rows)
SELECT AVG(primary_key) WHERE indexed_field=[somevalue] => 1s (~2M rows)
SELECT AVG(value) WHERE indexed_field=[anothervalue] => 0s (~15k rows)

I understand the first two use only the index without needed access to the actual data, which makes them super fast ; and the third one is also super fast thanks to the low amount of rows selected by the indexed_field.
What I can't figure out is why the initial query is so slow - MySQL is perfectly capable of processing millions of rows almost instantly (I just tried an AVG(random_field) on a 3M rows table and got the result in 3 seconds), I do provide it with the appropriate index to find them, so why does it take so long?
Here are also a few values out of the mysql config:

sort_buffer_size = 4M
key_buffer_size=48G
innodb_buffer_pool_size=96G
max_heap_table_size=12G
tmp_table_size=12G


texts: Pre-formated varchar variable does no work in select query

texts:
I am trying to run a select query that receives in the where clause the @Sites variable. The query at the moment is returning empty because the following line

AND S.[Name] IN (@Sites)

is not being processed as expected. Below an extract of the query:

SET QUOTED_IDENTIFIER OFF

DECLARE @Month int
, @Sites varchar(MAX) = null

SET @Month = 8
SET @Sites = 'Bayside;Collaborative'
SET @Sites = "'" + REPLACE(@Sites,';',"','") + "'"

SELECT
    CV.[Id] AS Id
    , ...
    , S.[Name] AS [Site]
    , ...
  FROM
    [CalendarViews] CV
    INNER JOIN [Sites] S ON S.[Id] = CV.[SiteId]
  WHERE
    MONTH(CV.[DateAllocation]) = @Month
    AND S.[Name] IN (@Sites)
  ORDER BY
     S.[Name]

When I run the query replacing

AND S.[Name] IN (@Sites)

for

AND S.[Name] IN ('Bayside','Collaborative')

the query works as expected. Can anybody tell me what I am missing please? Thank you.


texts: Reclaim disk space from large database's primary filegroup after truncating tables (other than shrink)

texts:
We have an 8 TB SQL Server 2014 SP1 database, with one primary filegroup, spread across multiple files on different drives. This is part of an AlwaysOn AG with multiple secondaries (some async).

One old table in this db is 6TB, and we truncated it. Now to reclaim this disk space, we can either DBCC ShrinkDatabase or ShrinkFile.

We don't care about the usual problems with shrinking like fragmentation, or database growing again etc. because we are only focusing on regaining disk space now.

But these shrink operations are REALLY slow, and are looking for a faster way to do this.

First, are these slow because the changes have to sync to the synchronized-secondary?

Second, is there a way to make shrinks faster?

We thought about couple other options:

Creating another database and moving all (except the old) tables to it...but might need to setup AG all over.
Creating another filegroup and moving all (except the old) table to it...but the old table is on Primary filegroup, which cannot be dropped, right ?

Which method have you guys used in the industry to 'shrink' databases after dropping or truncating large tables?

I have read How do shrink *right* when required? but no answer satisfies my questions.. is there an option other than shrink? Can I move tables off the primary filegroup and delete it?


texts: Mysql SHOW OPEN TABLES shows In_use but nothing in SHOW PROCESSLIST

texts:
I'm looking for advice for a MySQL database used for my development. I have a table is locked, although no process is active.

Here's some context: I am a developer on this MySQL development database without administration rights. All tables are on MyISAM. For the second time in two days I found that all processes accessing a specific table were waiting for a lock. I'm running those 30 concurrent processes on the applicative tier. Those processes are doing steady updates on this table (probably adding like 40 rows per second to this table). This table has a primary key, and one additional index. It has 6 columns including 2 with a blob. At the moment, the table contains 23.7 million rows, and is roughly 51GB in size.

When I found my 30 processes were all stuck due to this lock on this table, I killed them all first. Then I killed the still opened sessions by doing a SHOW PROCESSLIST and issuing KILL for all sessions. The problem is that even after killing those sessions,  the table in question still shows for SHOW OPEN TABLE a IN_USE value of 2, although SHOW PROCESSLIST is empty.

I can't figure out a reasonable reason why this would happen. There's no process to kill anymore now. I can't repair the table as the REPAIR TABLE waits for the lock. Yesterday night I had the issue, and this morning the locks were gone like magic. Now it's reoccuring again.

Does anybody have a clue on why this could be happening? Any hint for me or my DBA on the right approach to recover and avoid further occurrences of the problem?

EDIT: Strangely, by the time I wrote this, the IN_USE for this table decreased from 2 to 1. And for another table in the same database, moved from 0 to 1. As I'm not doing anything that could have explained the latter increase, I suspect MySQL might be doing something special under the hood.


texts: If an 'after' DDL trigger causes an error, is the DDL rolled back?

texts:
It has been suggested that DDL is logically performed something like this:

begin
    COMMIT;
    perform any appropriate pre-DDL trigger code;
    do the ddl;
    perform any appropriate post-DDL trigger code;
    COMMIT;
exception
    when others then
         ROLLBACK;
         raise;
end;

Which would suggest that any error in a trigger would cause the DDL to be rolled back. Is this the case?


texts: Get records not updated in last 30 minutes


texts: How Can I generate a data dictionary which contains the data types of a Json file.

texts: The data dictionary should be generated from a CSV file of sample data.


texts:
Is conditional execution possible for SQL Server Agent job steps?

That is if Step 1 returns 1 I want to go to Step 2.

If Step 1 returns 2 I want to go to Step 3.

If Step 1 returns 3 I want to go to Step 4.

It looks like it is not possible because the only distinction available for step completion is Success or Failure but I figured I would check with the experts here.

I know I can achieve this logic in SSIS package but was wondering if it can be done within SQL Server Agent.


texts: With MyISAM is there any index size savings when using INT vs BIGINT?

texts:
There is obviously a four byte per row space savings in a data column when using INT vs a BIGINT type.

But I have been trying to figure out if there is also any space savings for myisam indexes on integers since they are typically unpacked.

MySQL doesn't say what the structure type is for indexes - it only calls the units "nodes" and then doesn't mention their actual size.

So given the same set of numbers, say a million rows of integers, does using INT instead of BIGINT reduce the node size? Or because there can still be an unlimited number of rows despite the integer range, does the node size always have to be rather large?

I guess I could whip up a demo to test this question but maybe someone already understands this properly on a technical level and can explain it.


texts:
I am trying to copy my database from one server to another (postgres10). I have tried using the interface of pgadmin4 to take a backup, create empty database on the new server and use the interface restore option. This is the first time I have tried to move to a new server so I am not very experienced. The data got copied to the new server but the sequence was not, so when I try to insert a new item to the database the not null restriction makes it invalid.

Instead I tried to use the command line to backup and restore the database with these commands.

pg_dump -U postgres -Fc my_db > my_db.db

pg_restore -h <ip remote server> -p <port remote server> -U postgres -C my_db.db

While this worked to copy the data over, the issue still persist with sequence.
I have seen similar posts about this issue but not found anyone who has actually solved the problem. Is this a difficult task to complete? What I want is to copy by database over just as it is to another server.


texts: having 'Measure' > 0 syntax does not work in mdx query

texts:
Hope you're doing well
I've written an MDX query as you can see below :

with member [Measures].[TOTAL] AS
SUM(Periodstodate([DimTime].[Year].[(All)] ,[DimTime].[Year].currentmember)
,[Measures].[IndentCount])

Select {[Measures].[IndentCount],[Measures].[TOTAL]}
   having [Measures].[IndentCount] > 0 on 0 ,
   non empty [DimTime].[Year].[Year] on 1
from [Procurement]

The problem is despite using having [Measures].[IndentCount] > 0 I still see the null value for my measure as you can see below :

 .
I want to know what the problem would be???
I have this problem even when I use Filter function

Thanks in advance


texts:
how to identify table name using a column name if we are not aware about the table name

Imagine if you have  10000 tables in Database

I am aware about the column name , but i dont know the exact table name .

Could you please advise is there  a chance to find the table name  , using the column name which you are aware about it  already?


texts: SQL Server High Availability mode When I failover the node Permissions are going away

texts:
I created an Availability group with 2 Servers. When I fail over from one node to another the permissions are going away. (I granted a SQL server login DBO permissions)

So what I did was I wanted the remap permissions script to be run every time AG-Role change alert happens
If I run the Job manually after the role change permissions are working.
But Job is not running automatically everytime role change happens. It is failing with below error message

Unable to access availability database 'TestDB' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. [SQLSTATE 42000] (Error 983).  The step failed.

Is there any better way to handle the permissions issue when switching nodes in HA? How to fix the above error?


texts: Understanding swap impact on a small RDS with PostgreSQL

texts:
I have a db.t2.micro that regularly refresh materialized views with around 2 millions lines. I'm trying to understand how it's bad or not to have swap like this.

The instance is

When I fetch data it's very simple, because this database only store aggregates rows.

The PostgreSQL used 18 connections the last time I check.
I have default settings for the RDS for performances stuff

shared_buffers {DBInstanceClassMemory/32768}
effective_cache_size {DBInstanceClassMemory/16384}

And materialized biggest views look like this:

SELECT relkind   AS object_type
     , reltuples AS entries
     , pg_size_pretty(pg_table_size(oid)) AS size
FROM   pg_class
WHERE  relkind IN ('r', 'i', 'm')
ORDER  BY pg_table_size(oid) DESC
LIMIT 5;

Result

  name         object_type  entries size
  mview_1      m            1965325 76 MB
  mview_1_idx  i            1965325 74 MB
  mview_2      m            64368   2464 kB
  mview_2_idx  i            64368   1616 kB
  mview_3      m            15339   640 kB

What is the impact of a swap of 34m on a 1gb PostgreSQL RDS?


texts:
ALL,

First time post here in DB Administrators, so please be patient.

In the past long time ago I was working with Sybase SQL Anywhere on Windows. There I got the actual server, ISQL utility, and a little GUI application called SQL Central, which helped managing the database I was working for in the GUI mode.

Couple of days ago, I installed "big" Sybase - version 16 SP02 on Linux. To my surprise I saw only the server itself and the ISQL utility. No SQL Central or an application of that nature. Going thru the docs, I also didn't see anything.

Now my question is: is SQL Central an outdated software? Or maybe it is only supplied with SQL Anywhere? This utility was very helpful and I was even running ISQL from it if I needed to run some queries on the DB

I don't mind just using ISQL but that application was very helpful.

Thank you for any hints you can provide.


texts: Arguments for MongoDB backup with OpsManager or mongodump?

texts:
I have a replica set of two MongoDB nodes running (with an arbiter) and I am now wondering about the best backup and recovery solution. I am aware of the official documentation, but it does not provide me enough pro and con's for each solution. In particular, our team wants to decide between either a OpsManager-solution or a mongodump. I am not experienced with either of them, so could you be so kind and please help me out with more arguments and experiences? The following arguments I collected so far, feel free to edit them if they are not correct:

Arguments for mongodump and against OpsManager

simpler, just a command line for a snapshot and another one for the recovery
no additional servers and/or licenses required which is the suggested solution for OpsManager

Arguments for OpsManager and against mongodump

tested solution, provided directly by the team who developed MongoDB
cleaner and sustainable, since it could be configured via GUI and does not require command line access
easy logging of each backup run possible, which contrasts to mongodump requiring a custom (power?) shell script wrapped around

Related questions on Stackexchange which did not solve my issue:

https://stackoverflow.com/questions/22555280/best-way-to-make-hot-backup-mongodbtokumx-database-instance
https://stackoverflow.com/questions/31502601/how-should-i-backup-mongodb-on-digitalocean
https://stackoverflow.com/questions/39169209/backup-mongodb-data
https://stackoverflow.com/questions/17458124/how-to-get-a-consistent-mongodb-backup-for-a-single-node-setup
Backup with mongoexport or mongodump?


texts: How to make inter-database queries with dynamic SQL Server database name?

texts:
We have two database servers with SQL Server 2005, with two databases each. The secondary server have two SQL Server instances: one for development and other for reporting. Both instances on secondary server restore backups daily from the primary server, but only the development instance could have data modifications.

The two databases are from different applications, but one database has triggers that alter data on the other database. Triggers update the second database hardcoding its name, like UPDATE database2..thetable
One database can fetch data from another via SQL queries from one application or Stored Procedures, hardcoded as SELECT FROM database2..thetable

We are considering joining the two SQL Server instances on secondary server (reporting and development) to simplify administration and memory managing, but I couldn't find a solution to make triggers and stored procedures calls another database dynamically and automatically with another name using the same suffix. Ex:

Instance: RS

DB1 (calls DB2 with DB2..table)
DB2

Instance: DEV

DB1 (calls DB2 with DB2..table)
DB2

Into:

Instance: default

DB1_RS (calls DB2_RS with DB2_RS..table)
DB2_RS
DB1_DEV (calls DB2_DEV with DB2_DEV..table)
DB2_DEV

What are the possible solutions to this? I can think about some, but they are too convoluted:

Create dynamically T-SQL to be executed via EXEC(). This makes thing more complex because the triggers have lots of code for UPDATES and INSERTS and DELETES.
Create separate triggers and stored procedures for each group of database names (one for DB1, other for DB1_DEV, other for DB1_RS). This approach seems to not scale...
Make the database name dynamic on the query: INSERT INTO @db..table. Unfortunately SQL Server doesn't support this... :-(

Is there a simpler and effective solution to this?

Thanks!


texts: What's wrong with this update RANK query?

texts:
It was updated only the first time I ran it, after that the users_tbl_rank stays the same.

$sql = "SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC";

In my table, users_tbl_rank and users_tbl_points are both integers.

Could anyone tell me what I'm doing wrong here? Thanks in advance.


texts: Max redo speed on replica side in Availability Group

texts:
I heard about maximum 50 Mb/sec for receive and 50Mb/sec for redo.
But really, I have only 20Mb/sec redo speed. This is speed from Perfmon, so I know about the following issue:

AlwaysOn Availability Groups log_send_rate

As I know redo operation depends on CPU and for SQL Server 2012 it's run use only one core of CPU. I think it’s awful for situation when I can generate a lot of logs. For ex. when reorganize a lot of indexes in big tables.

Do you know about any KB or advise for improving performance for this case?


texts: Number of connection running within certain period of time?

texts: Is there any way to capture number of connections or processes which is running on the prod server each 10 mins of interval? And MySQL version is 5.1.47.


texts:
I need to read more rows than fit in memory, resonably fast. I see two options:

SELECT ... where my bindings use a cursor to stream the rows;
COPY (SELECT ...) TO STDOUT (FORMAT binary) where my bindings decode the binary format into rows.

What are the theoretical differences in performance and behaviour between the above approaches, or are they effectively identical?


texts:
So we have seven servers, one for each of our locations, each server has the exact same schemas/table setup. We were making a reporting server to be able to look at the data from all sites at once. In order to do this at first what we did was a daily load of each server unto the reporting server. We added a SourceServerID column to each table and had that generated by the SSIS package. These were put into a database named ODS.

Our seven servers are going through an upgrade right now. With this upgrade changes are being made that will allow us to use replication instead of the daily load like we were before. With the replication we are creating a staging database on the reporting server for each of the seven servers naming them like SourceServer1. Then creating views on the ODS that do simple Select... Union... type of query to combine the data from all the SourceServerX databases.

This is causing the reports we run to go much slower (ex: from 5 min. to 2 hours).

Questions

Could the slow down be due to pulling from the multiple SourceServerX databases as opposed to the one ODS database like before even though these are all on the same instance of SQL Server?
If we combined the tables into one database and then added the SourceServer name to the front of the tables would the views then run faster?
With replication could we combine the tables from all the SourceServers into one set of tables adding in the SourceServerID column like we did for the daily load?
Is there a different method that would allow for better performance while maintaining the up to date data that replication provides?

Note: I had first put this on Stack Overflow (see https://stackoverflow.com/questions/45017951/database-merging-and-replication) but realized this might be a better location for this question.


texts:
I have been engineering and developing a NoSQL database engine (using C#.Net) for .Net technologies. Similar to the available NoSQL database, I store all documents in JSON format, and I keep all related document records (i.e. Album1, Album2, Album3) in a single file.

I have been testing the solution to see its actual performance in a real world scenario, and using Visual Studio Unit Testing framework, my solution has managed to query and search 2,000,000 documents in approximately 12 seconds.

My first question is, how good is that performance if it is a good result at all?

Secondly, since all records are eventually saved in files, I have implemented a singleton design pattern that buffers up all physical documents in memory in order to prevent the need for concurrent file processing. At 2,000,000 and assuming there are 10 document categories (Album, Genre, Artists,...) this will consume more than 8GB of RAM which is not good. On the other hand, if I disable the buffering and make all queries dependent on searching the physical files, assuming that I get 100 concurrent request and each take 12 seconds to complete, these 100 request may take up to 1,200 seconds to complete which is I think is terrible.

The second question is, how could this be optimized? I mean, a NoSQL database, unlike a SQL database, is meant to preserve massive data, and such massive data cannot be fully buffered in RAM or be searched over and over on disk. In theory, how should this be implemented?


texts: I will open my SQLPlus with CMD in windows. When I write a query it shows 157998 rows as result. I want see the all result in sqlpus window, but it is not showing all the result.


texts: Removing MySQL Orphan Tables in Google's Managed MySQL

texts:
I have a number of orphan tables that I am unable to remove in Google managed MySQL instance.

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html describes how to find them
using the query:
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

and returns the following results

TABLE_ID, NAME, FLAG, N_COLS, SPACE, FILE_FORMAT, ROW_FORMAT, ZIP_PAGE_SIZE, SPACE_TYPE
'10790334', 's/#sql-ib10651620-992396264', '33', '10', '15069', 'Barracuda', 'Dynamic', '0', 'Single'
'11386494', 's/#sql-ib11176578-758128583', '33', '10', '15865', 'Barracuda', 'Dynamic', '0', 'Single'
'11715795', 's/#sql-ib11550885-968245244', '33', '56', '16593', 'Barracuda', 'Dynamic', '0', 'Single'
'11884112', 's/#sql-ib11854996-1155811138', '33', '63', '16936', 'Barracuda', 'Dynamic', '0', 'Single'
'12648506', 's/#sql-ib11883996-1143057998', '33', '31', '18549', 'Barracuda', 'Dynamic', '0', 'Single'
'12877658', 's/#sql-ib11884055-1196159549', '33', '34', '19856', 'Barracuda', 'Dynamic', '0', 'Single'
'15159365', 's/#sql-ib15000139-4079941189', '33', '58', '24560', 'Barracuda', 'Dynamic', '0', 'Single'

I am unable to select any of these tables, for example:

use s; SELECT * FROM `#mysql50##sql-ib10651620-992396264` limit 10;

Following https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/,
I tried creating a table with the same name.

I figured out which tables these orphans came from with help from the query

SELECT * FROM
 information_schema.innodb_sys_tables
 JOIN information_schema.innodb_sys_columns USING (TABLE_ID)
 WHERE information_schema.innodb_sys_tables.NAME LIKE '%#sql%'

I then copied the create table statement for the related table and edited the table name.

CREATE TABLE `s`.`#sql-ib10651620-992396264` ( `ID` int(11) NOT NULL AUTO_INCREMENT, ...) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and then tried to drop it

drop table `s`.`#mysql50##sql-ib10651620-992396264`;

but it could not find the table.  After dropping the created table with

drop table `#sql-ib10651620-992396264`;

the orphan table still persists.

To my knowledge, with a managed MySQL instance, I do not have access to the machine that MySQL is running on and therefore cannot remove the associated files from the command line.  Logging into the database from the command line and executing a shell command, executes a command on the client's host system, not the system that MySQL is hosted on.

Not a huge issue, but it would be nice to reclaim this disk space.


texts: Can sql server logins stored in the master db/msdb be used for migration?

texts:
In sql server when we expand security, expand logins, there is list of logins.
Are all these stored in the master db?
If so, then when migrating to another server, if we backup this master db and restore to the new server, then will the logins get migrated to the new server?
Similarly SQL server jobs, schedules, etc are stored in the msdb? Can this be backup/restored to migrate the jobs to the new server?
If not, then what is the purpose of doing a backup of these system dbs?


texts:
I created simple plan

with my access to Ms SQL Server 2008 R2

but job failed with exception

  Date      31. 3. 2013 4:00:01 Log     SQL Server Agent (Current - 17. 3. 2013
  5:00:00)

  Message [298] SQLServer Error: 15404, Could not obtain information
  about Windows NT group/user 'Xxx\Xxxx', error code 0x2. [SQLSTATE
  42000] (ConnIsLoginSysAdmin)

('Xxx\Xxxx' is domain/DNS_Name)

with the same exception for another type of jobs, e.g. BackUp
I'm able to execute all a.m. events from Management Studio (for example) without any issue

please is there any issue with my access


texts:
I have 5 tables that contain data split by quarter each table has a similar structure to the following:

ID_Column bigint PK,
EventType int,
DateStamp datetime PK
CustomerId int,
ARG_1 nvarchar(32),
ARG_2 nvarchar(32)

There is also a CHECK constraint on each table to ensure that the DateStamp is within the specified quarter.  I have then created a View across these tables which I am querying against.

I need to execute several queries which aggregate the date by week, month, 3 months and 12 months.  When I look at the Query Plan for my query it is querying all tables regardless of the filter on the DateStamp column.

I have a controller Stored Proc which in turns calls a Processing stored procedure. The processing stored procedure accepts the start and date as parameters, assigns them to local variables within the procedure and runs the query.

The query is similar to the following:

Select v.ARG_1,
       t.SomeColumn,
       Count(Distinct(v.Customer_Id))

From  dbo.My_View v
Join  dbo.AnotherTable t on v.ARG_2 = v.PKColumn
Where v.DateStamp >= @StartDate
And   v.DateStamp <= @EndDate
And   v.EventType = 1
And   t.SomeColumn = 'A_VALUE'
GROUP BY v.ARG_1, t.SomeColummn

I have also noticed that the estimated row count is wildly inaccurate (Estimated rows: 13 million vs Actual rows : 170 million). This confuses me as the statistics are up to date and I have rebuilt the indexes.

The performance of the query is acceptable for my purposes but I am wondering what could be the reason for these oddities?

EDIT: I am running SQL Server 2012 Standard Edition.

The anonymized XML Query plan can be found here


texts: BACKUP failed to complete - with spaces, for no reason

texts:
Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure in a query window with a WAITFOR in place just to check. In the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG the message is:

2011-12-28 07:49:52.43 Backup      Error: 3041, Severity: 16, State: 1.
2011-12-28 07:49:52.43 Backup      BACKUP failed to complete the command BACKUP DATABASE                                                                                . Check the backup application log for detailed messages.

Obs: the log actually contains all these spaces and no further info.


texts: How to connect mongodb from aws ec2 ubuntu docker containers?

texts:
I have tried

mongosqld --addr localhost:3002 --mongo-uri xxxxx.com:27017

But it is throwing the following error:

  unable to load MongoDB information:

  failed to create admin session for loading server cluster information: unable to execute command: server selection error: server selection timeout current topology: Type: Unknown


texts: How to Track The Server Usage by The PID?

texts:
MySql is taking too much resource in one of my client's server. They are running a website developed in php. I have attached the screenshot below. Single process only taking too much resource.
I want to track that usage by the PID. its possible or not? if its possible please help me to track it.

Thank You!


texts: Configuring Dynamics GP's (SQL Server) Disks

texts:
I am an accidental DBA at my work, and when I took it over, it was quiet a mess.  Fortunately, we have the green light to build new systems for an upgrade to our accounting system:  Microsoft Dynamics GP.  I would like to follow or exceed all recommendations from Microsoft.  Our current environment has significant performance issues.  I would like to get this forum's opinion of our SA's plan for our new system's configuration, compared to what is recommended by Microsoft.

As far as disk configuration, Microsoft recommends:

OS/APP RAID 1
SQL Log RAID 1
SQL Data RAID 5
SQL TempDB RAID 1
SQL Backups RAID 0.

What our SA has available is: A VMware 6 datacenter with an VNXe 3200 SAN.  That SAN has 11  x 600 GB 10k RPM drives and 9 x 2TB 7.2k RPM drives.  It also has 6 x 200GB flash drives to be used as very fast cache of the most frequently accessed data.  So the recommended hard disk specs don’t seem to take into account a datacenter that is as virtualized as ours.  He has pooled all the 10k RPM drives into a RAID 6 + 1 hot spare and all of the 7.2k RPM drives into a second RAID 6 + 1 hot spare and labelled them Performance Pool and Capacity Pool, respectively.  The more platters he puts into a volume, the more performance he can get out of it, plus all the redundancy that comes with a RAID 6 and a hot spare.  However, he would like to house a significant portion of the database in that cache.

Given that our accounting system and its SQL Server installation will not be alone in this configuration, but mixed together with other company systems (such as the exchange server), is this satisfactory or should I push that the accounting system have its own separate RAID configuration more in line with Microsoft's recommendations (except the RAID 5 part...I heard a puppy dies every time RAID 5 is used)?


texts: How to check the status of log file restore for SQL Server

texts:
Used this script to restore a full backup (about 30GB of data and 100GB log) on a SQL Server 2012 box:

RESTORE DATABASE [dbname] FROM DISK = N'S:\import\dbname_201505131100.bak' WITH FILE = 1, MOVE N'dbname' TO N'F:\SQL\INST1\DATA\dbname.mdf', MOVE N'dbname_log' TO N'G:\SQL\INST1\LOG\dbname_log.ldf', NOUNLOAD, STATS = 1

98 percent processed.
99 percent processed.
100 percent processed.
... and nothing else for another 20 mins until "RESTORE DATABASE successfully processed...".

All this time, the query window showed "Executing query...", there was no more I/O on the data files drive (F:), but a lot of writing on the log file drive (G:). sys.dm_exec_requests showed percent_complete 100 and status suspended.

Looks like there's no insight into the status of the log file restore. As the log restore takes more that 2 thirds of the total restore time in this scenario, any way to check the progress ?


texts: Mixing SQL Server 2016 and 2017 in a distributed availability group

texts:
I have an old Availability Group made up of 2 SQLServer 2016 instances (2 Windows 2016 servers in WSFC)
I also have 2 new SQLServer 2017 instances (2 Windows Server 2016) that I originally wanted to join to the 2016 AG.

This is a 0 downtime migration scenario and the 2016 servers should be dismissed once the DBs were aligned on the 2017 ones.

To my great disappointment, I discovered that it is not possible to join the 2017 instances to an existing 2016 AG, but I cannot afford the burden of stopping production, taking and restoring a backup, waiting for DBs synchronization, changing the name (and possibly the ip) of the new AG to match the original one
unless as the very last resource...

Then I came across the new 2016 service called "Distributed AG Group" and I started thinking about using it for my migration scenario ... basically like this:

Create a new AG with the SQL 2017 instances
Create a Distributed AG between the original 2016 AG and the new 2017 AG (applications continue to connect to the 2016's listener)
Wait for the DBs synchronization to take place in the 2017 AG
Make the 2017 AG as the primary
Remove the 2016 AG from the Distributed AG (short applications downtime)
Change the name and the ip of the 2017's listener (applications are up again)
Remove the distributed AG

It is feasible? Can I mix 2016 and 2017 AGs in a Distributed AG?
Or there is (obviously!) something that I'm missing and there's a simpler or more proper way to make it?


texts: Optimizing the table to hold a million records

texts:
I want to use a table with a column that should have a million records and it has to retrieve the results very fast.

Im going to use it in a query like below

  Database : MySQL

select tags from tags_master where tags like 'A%';

Im trying to use it in typeahead. i tried with sample data, but for a million entries it is taking so much time.

I already tried appying indexes on that field. is there any other trick to optimize it further for typeahead purposes ?

Thanks in advance.


texts: Update records that match like regex from another table

texts:
I have two tables - ProductCategories and Products:

ProductCategories
---
productid    categoryid
1
2
3
4

Products
--------
productid    productname
1            Apple
2            Apricot
3            Tomato
4            Watermelon

and I would like to populate categoryid in table ProductCategories in such manner that all products having names starting with "Ap" are set to categoryid 1, starting with "To" to category 2 and starting with "Wa" to category 3 resulting with table ProductCategories populated as follows

ProductCategories
---
productid    categoryid
1            1
2            1
3            2
4            3

I have tried this

UPDATE ProductCategories
SET categoryid = '1'
WHERE product_id IN
      (SELECT product_id
       FROM Products
       WHERE productname LIKE 'Ap%')

but query does nothing. I would have run modified query few times for other options ("To", "Wa", etc) to completely populate ProductCategories table.


texts: Optimization for aggregation and grouping with MySQL

texts:
I have an application that is pulling data on a daily basis from numerous APIs, storing it and then presenting it on the front end in various charts, tables, etc..

I'm having some issues avoiding Using temporary and Using filesort with some of the queries being run on a database.

Now, I know that sometimes this is unavoidable. Should I even bother changing it in this situation?

The application caches the requests that run the most of these queries, so they won't be run often at all. However for the sake of best practices and learning I want to make sure that I'm running things optimally.

Here's what's causing it. There's an assortment of tables containing various types of data. Assume a table structure where most of the columns are data that can be aggregated (SUM, AVG, etc) and any other columns are either ignored or grouped. One column consistent across the report data tables is a DATE column. As the data is pulled daily from APIs, the data in each table is being stored for said date.

The majority of the front end components are currently displaying data to the end users at a monthly detail level (daily is needed though for upcoming features -- this level of detail cannot be compromised).

I'm currently using MySQL to group and aggregate data.

Here's a basic illustration:

SELECT
    DATE_FORMAT(c.date, '%Y-%m') as date,
    SUM(c.conversions) as conversions
FROM data_ga_conversions c
WHERE c.goal_id = 1 AND c.date BETWEEN '2014-10-01' AND '2015-02-28'
GROUP BY YEAR(c.date), MONTH(c.date)

Note the GROUP BY clause using date functions. As I'm sure you know -- this is what's causing the query executor to use temporary and filesort. (Note: I'm aware that I can get rid of filesort by adding ORDER BY NULL, however I'm trying to keep these basic sorting and aggregation calculations in one place, application code or SQL.)

Am I approaching this the wrong way? Here's a couple of alternate methods I had considered:

Storing year, month and day in separate columns.
Aggregate tables (à la materialized views... switching to postgresql is a potential option too).
Let the application do it

Thanks!


texts: Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?

texts:
I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a column in this database might be of UDDT bTinyInt(TinyInt).
Two tables in this database have columns that share the same exact UDDT. E.g. TableA.Field1 is of type bVarchar(VARCHAR(10)) and TableB.Field2 is also bVarchar(VARCHAR(10)).
When I join these two tables together by the aforementioned fields, I notice a warning in the execution plan stating a Cardinality Issue because these fields are being implicitly converted to VARCHAR(MAX).
Is it always the case that UDDTs get implicitly converted when used as predicates, even when comparing the same UDDT?
Also not sure if it matters as well, but the database these two tables belong to is of collation Latin1_General_BIN.


texts: MySQL automatic import of (huge) csv files fails using mysqlimport

texts:
For testing purposes, I have to insert large Time Series data on a MySQL database, running on a Windows remote server.
Firstly, the (very basic) table:

CREATE TABLE 'sensor_data' (
    'unix_timestamp' bigint(20), -- unixtimestamp in ns precision, 19 digits
    'sensor' varchar(45),
    'val' double,
    'tag1' varchar(20),
    'tag1' varchar(20),
    'tag1' varchar(20)
)

I have some CSV files, with 300 millions lines (nearly 15 GB), with comma-separated fields, with the same exact columns, in the same order of the created table. For example:

unix_timestamp,sensor,val,tag1,tag2,tag3
1598362837498000000,sensorname,0.333,info1,info2,info3
....

I tried the mysqlimport tool, with the following Powershell command (from a client pc on the same LAN):

.\mysqlimport -v --fields-terminated-by=',' --lines-terminated-by='\r\n' --ignore-lines=1 -u user -p --local -h host db path/to/csv/tablename.csv --columns=unix_timestamp,sensor,val,tag1,tag2,tag3 --force --use-threads=4 --port 3306

The result is always the same:

Connecting to host
Selecting database db
Loading data from LOCAL file:
path/to/csv/tablename.csv into tablename
db.tablename: Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
Disconnecting from host

When I add the -? flag to the same cmd, the explain function describes all my parameters, and they are all ok.

Weird fact: When I try to import much smaller CSV files (e.g. 400 lines), import succeeds, all lines get imported, but the result is:

unix_timestamp         sensor                             val tag1 tag2 tag3
1598362837498000000    sensorname,0.333,info1,info2,info3
....

Timestamp column is ok, but all other fields are written in sensor field!

How can I fix both weird behaviors?

Please note that I need a way to automate ingestions, so I cannot use interactive ways of inserting data.


texts: MySQL, comparison between, tables with not all records common, on 2 time intervals

texts:
I want to make comparisons on the volume of sales, turnover and margin of all product sales
between 2 time intervals (2017-10-1 to 2017-10-31 ) and (2018-10-1 to 2018-10-31).

for example:

Product Name | Volume 2017 | Volume 2018 | Margin 2017 | Margin 2018 |
     A       |     200     |     100     |     10      |      12     |
     B       |     190     |     120     |     23      |      20     |
     C       |  'no sales' |     100     |  'no sales' |      12     |
     D       |     300     |  'no sales' |     25      |  'no sales' |

My approach is the following:

select
    /* attempt to get either name if null but doesnt work as I join product ID's*/
    if(ifnull(q1.ProductName,false), q2.ProductName , q1.ProductName) as 'Name'
    ,q1.NumofProductsSold as 'Volume 2017'
    ,q2.NumofProductsSold as 'Volume 2018'

    ,format(q1.Margin,2) as 'Margin 2017'
    ,format(q2.Margin,2) as 'Margin 2018'

    ,q1.TotalTurnOver as 'Turnover 2017'
    ,q2.TotalTurnOver as 'Turnover 2018'

from

(   select

    format(sum(oi.product_quantity),0) as 'NumofProductsSold'
    ,format(sum(oi.turnover),2) as 'TotalTurnOver',
    ,SUM(oi.total_Mark_up) + o_inf.Markup 'Margin'
    ,order_id
     ,ifnull(p.name, 'empty') as 'ProductName'
     ,p.id as 'PID'
     ,op.date as 'date'

     FROM order_items oi JOIN products p ON p.id = op.product_id
                        join product_categories pc on p.id = pc.product_id
                        join orders o on op.order_id = o.id
                        join order_info o_inf on o.order_number = o_all.orderNumber

     where pc.category_id = 1 -- cat A products only
         AND op.order_status != 'CNL' -- not canceled
           AND op.date >= '2017-09-01' AND op.date <= '2017-10-31'

     group by p.id) as q1

left join

(  Select

            /* same query BUT for 2018 date range */

 ) as q2

on q1.PID = q2.PID  -- join on product ID

My issue is that if a product doesn't have any sales in either/both the first or second time interval, I get only the rows that correspond on both, so I always "miss" some products if they have sales in one time interval and not the other.

With left/right join I can get correct results only if either set is a subset of the other respectively, i.e if a product ID appears in the right and not the left it doesn't appear with left join. I tried UNION it works BUT it doesn't produce the outcome in specified in the description above. Is there a workaround based on how I designed the Query or should I follow a different approach?

All suggestions-corrections-critique more than welcome! Thanks!


texts: Row level security in PostgreSQL for different groups

texts:
Need a little help with row-level security in Postgres.

I have several tables and three group roles (group1, group2, and group3). I have several login roles that are members of each group, while "admins" are members of all groups. I would like to enable row-level security such that members of group1 can only see rows that were created by them or another user in group1 -- and same for group2 and group3. I can easily do this for individual users by creating a row that contains current_user and using something like "CREATE POLICY row_policy ON table1 USING (true) WITH CHECK (created_by = current_user);", but how does one enable this for different group roles so that every member of that group has the same row-level privileges?


texts: db2rspgn generates TYPE = ese for workgroup server edition, is that normal?

texts:
I tried to run db2rspgn on a workgroup server instance and to my surprise <instance>.TYPE = ese in the generated response file

]$ db2licm -l
Product name:                     "DB2 Workgroup Server Edition"

]$ db2rspgn -d /opt/nya/users/db2inst1/generated_responsfile/ -i   db2inst1  -t trc

]$ grep TYPE db2server.rsp
INSTALL_TYPE = CUSTOM
db2inst1.TYPE = ese

I also note that the response file contains:

COMP = ACS

which is also puzzling, since the reason I like to create a response file is because I would like to add support for ACS.

Does anyone have a clue why the file is generated the way it is, and am I missing something obvious when I'm generating the response file?


texts: "set-variable = innodb_thread_concurrency=4" or "innodb_thread_concurrency=4"

texts:
Based on this question, the asker wrote:

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
set-variable = innodb_flush_log_at_trx_commit=0

All of the variable has set-variable. I've tested this way on MySQL 5.1 standard install, and it works fine.

But I dont like it that way because it too long.

Lots of other guide including in MySQL website didn't say about using set-variable. Even in MyISAM setting example (MySQL 5.1), it only use thread_concurrency = 10

I've just installed MySQL 5.5 on new server and now is the time for configuration.

So, do MySQL really need set-variable?


texts: Vertica DB user permissions becomes active only after connection is closed

texts: I am using Vertica Analytic Database v7.1.1-12. I faced the issue then I am granting permissions to the role and then assigning role to the user and the permissions are finally granted only after I close the connection. Is there any way to flush privileges or something like that?


texts:
I'm trying to get percentage of payments according to status every day. I found a MySQL query which throws an error at Oracle when I use '=' inside SUM parentheses:

"ORA-00907: missing right parenthesis"

How could I convert this into Oracle accepted script?

SELECT pdate,
       COUNT(status) total,
       SUM(status='successful') successful,
       SUM(status='failed') failed,
       SUM(status='refunded') refunded,
       SUM(status='successful')/COUNT(status)*100 successful_percent,
       SUM(status='failed')/COUNT(status)*100 failed_percent,
       SUM(status='refunded')/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate;


texts: What happens when you update a column which should not be updated? Are rows updated completely?

texts:
Let's start with the bottom-line question. Say I have an UPDATE statement as following:

UPDATE my_table
SET    my_column = <some new value>,
       my_other_column = my_other_column

Is there a performance penalty for the unnecessary my_other_column = my_other_column assignment?

Now, to the real scenario. In my case, I have a function update(a, b) as following:

create function update(integer, integer) returns void as
$$
    UPDATE my_table
    SET    a = case when $1 is not null then $1 else a end,
           b = case when $2 is not null then $2 else b end
$$
language sql volatile;

I am wondering what happens when I call select update(150, null). This resolves to:

UPDATE my_table
SET    a = 150,
       b = b

Is there a performance hit for that redundant b = b assignment? Are we now re-writing the b column, instead of only the a column if we were to remove that assignment from the UPDATE statement? Or are rows always written as a whole anyway, regardless of the actual updated columns? Or maybe, is the planner or executor smart enough to filter such redundant assignments out?


texts: Is it possible to keep track of which rows were selected and fetched last in MySQL?

texts:
I am working on a small website using Java where the user posts are stored in MySQL, I am getting the posts 5 at a time (5 rows using LIMIT 5) by most recent one (using timestamp DESC) and send it to the front-end .

The problem I am trying to solve is to be able to fetch the next 5 that are not the same rows that were fetched first...and so on. So I need to find away to keep track of which rows were selected and fetched in the last query and avoid getting them again.

The idea is that when the user scrolls down to the end, AJAX will send a request to servlet and the servlet will get the next 5 rows from MySQL.

I've been trying to find away by experimenting with the database as well as some online search, could not find a way so far.


texts: can any tweaks improve full table scan time in Postgres/Cloud SQL?

texts:
I have a table with 150 million rows in a Postgres database hosted in Cloud SQL (in Google Cloud Platform).
Typically I obviously try to avoid scanning the table, but occasionally I will manually want to perform a query that I think will always produce a table scan. For example, SELECT COUNT(*) FROM table WHERE col1 IS NOT NULL. Most recently I was running that query out of curiosity, to see if I had set any values yet, but it essentially ran forever.
Fortunately the only time I run into this need is speculative manual queries like that, but it got me curious if there's anything I can do to improve the performance, or if I'm basically stuck with a table that I can't perform certain queries on due to its size.
Right now I have 4 cores, 500GB storage, 15GB RAM, but it seems unlikely that boosting CPU or RAM would help (as-is, that query only consumes 1-2 cores). I might benefit from greater IO capacity, but at least with Cloud SQL hosting, 500GB storage maxes out the associated IO capacity of a 4 core box. You need to go to something like 14 cores to have a shot at any higher IO specs.
As far as I can tell, partitioning wouldn't help here either. It seems like that would help if I could somehow cause each partition to get its own physical SSD and parallelize reads across more hardware that way, but my sense is that partitioning is more about finding ways to reduce the total number of operations that are needed, than about taking advantage of separate parallel SSDs.
Just curious if there's anything I might be missing here.


texts: Can I connect two mysql database located in different server by php?

texts:
I want to connect two mysql database from different server. Is it possible..

Please give me a example if yes. I have bluehost and godaddy server.


texts: How to add a column with a foreign key constraint to a table that already exists?

texts:
I have the following tables,

CREATE TABLE users (id int PRIMARY KEY);

-- already exists with data
CREATE TABLE message ();

How do I alter messages table such that,

a new column called sender is added to it
where sender is a foreign key referencing the users table

This didn't work

# ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;
ERROR:  column "sender" referenced in foreign key constraint does not exist

Does this statement not create the column as well?


texts: How to deal with id gap when your query is like from id > XX to id < XXX

texts:
I have a large table of around 50M data. I am trying to get data with something like

select id,name,address,city
from table_name
where id > 50000000 and id < 50000050
order by id;

But, this strategy could work only when there is no deleted data. However, In my case, I am soft-deleting data so whenever a row is deleted, its column deleted_at will be filled with current timestamp i.e when deleted deleted_at is not null.

Now, how can I tackle this situation? Please, suggest me what would be my best bet for this.

I am ready to:

create the index with an extra column(if possible) and reorder on every deletion
create a temp table with extra table and reorder it on every deletion
Or same trick with view
or some other way.


texts: How can I setup replication with the DNS name instead of the Server Name

texts:
We are using SSL certificates from a public CA on each SQL Server in our (push) transactional replication topology, and wish to have the EncryptionLevel = 2 for our Distributor Agents. Currently EncryptionLevel is set to 1, and works fine. (The difference between 1 and 2 is that 1 tells the agent to just to use encryption, and 2 tells it to also validate the certification chain of the SSL certificates)

The SSL certs are from a public CA. The Subject Name is the full Windows FQDN of the server (SERVER1.mywindowsdomain.com) and the Subject Alternative Names list contains the full DNS FQDN (MYAPP.MYDOMAIN.COM).

I can confirm that using SSMS I can correctly connect to each SQL Server without ticking 'Trust Server certificate' in the connection dialog, meaning that the certificate chain is being properly validated successfully.

When I change the EncryptionLevel to = 2, our Distribution Agents can no longer connect to the Publisher/Distributor running on the same server. This would make sense because they are trying to connect to SERVER1\DEV, whereas the SSL certificate is for a public DNS name - MYAPP.MYDOMAIN.COM, and therefore chain validation fails.

I am therefore looking for a way to setup replication from scratch, but using the DNS name (MYAPP.MYDOMAIN.COM) instead of the machine name (SERVER1).

However, when I execute the following command:

exec sp_adddistributor @distributor = N'MYAPP.MYDOMAIN.COM\DEV', @password = N'12345'

Then I get the following error message back:

  Msg 18483, Level 14, State 1, Line 48 Could not connect to server
  'SERVER1\DEV' because 'distributor_admin' is not defined as a
  remote login at the server. Verify that you have specified the correct
  login name. . Msg 14114, Level 16, State 1, Procedure
  sp_adddistributiondb, Line 169 [Batch Start Line 65] The server
  'SERVER1\DEV' is not configured as a Distributor.

Note that this command worked fine in the past when it was this:

exec sp_adddistributor @distributor = N'SERVER1\DEV', @password = N'12345'

After much googling, I have tried many variations on this. I have tried:

Using Client Aliases (32+64 bit) on the Publisher
Varying how my SSMS session is connected to the server, i.e. the connection dialog server name. I've tried using SERVER1\DEV, MYAPP.MYDOMAIN.COM\DEV, and also SERVER1.mywindowsdomain.com\DEV.

Any suggestions on what to try next would be appreciated. I'd be happy to explore either a) getting the Distribution Agents to be able to validate the certificate of the local server [but keeping in mind I cannot modify the SSL certs], or b) re-creating replication entirely from scratch so that it uses the DNS FQDN's, ie. MYAPP.MYDOMAIN.COM\DEV.

 Note: Just to head off anyone that googles that error message and suggests that our SQL server might have been renamed, that is definitely not the case. The issue is the machine name vs the DNS name.


texts:
Normally we can skip 1062 duplicate error  but in rds can we skip multiple 1062 errors in one go.

For example we define slave skip error =1062 in my.cnf and restart slave. but in RDS MYSQL how can we permanently skip 1062 error, alteast for few hours as we have duplicate data in the restore file and unfortunately log position we have is old.


texts: Custom Aggregate Postgresql C extension with input as initial condition

texts:
Is there a way to write a custom aggregate Postgresql C extension function that takes an input from the user and sets it as the initial condition? If so, how?

I’ve written several Postgresql Extensions in the past. The inputs are always columns of the table, but now it is requirement —for theoretical reasons- that I need to make an aggregate extension dependent on one user input that changes a lot. In particular, this values cannot be stored in the database either.

Edit 1:  For example, say I want to run the function

aggregate fancy_select( int tmp_accum_value, int column, int *fancy_math_stuff){

return (row *** fancy_math_stuff)
}

where *** is a fancy math operation that uses the entire fancy_math_stuff array and said array is passed on the first call but it is not stored as column in the database. tmp_accum_value is the intermediary state of the accumulation.


texts: What is difference between starting Instance with -T3608 and with -f?

texts:
I simulated a corruption of model database on test environment just for learning purposes. While reading others blogs and articles about it I stuck on the question, what is difference between starting SQL Server Service with undocumented -T3608 and -f flags?
When I tried to start service with -f, then got error message and service didn't start. However, in case of -T3608, it started smoothly. For -T3608 flag, I read that, -T3608 starts all databases without performing recovery and -f starts instance with minimal configurations. What is minimal configuration, I can't find info about it.


texts: Update field in SQL Server Table Dynamicaly

texts:
I need to do a 1 time update of a Sql Server table.

The table has a unique ID, contains a member number field and a FileName field which tells us what file the member data came from.

What I am needing to do is update the [Filename] value to be different for all the records pertaining to a specific member number that was present in the historical file more than once.

So in the image below Member number 123 has 3 records in the table that have FileName value as history. I need the value of Filename to be unique per member number record so I would need the Filename values for this member to be History, History_2 and History_3, instead of History for all 3 records.

I'm pretty sure this is possible to do with SQL and am currently working on it but was wondering if someone has come across a similar table update need and had some advise or code I can slightly modify to get my desired results.

This is the code I use to identify the member number that have records that need to be updated:

SELECT Member_Number, COUNT(*) CountNumber
                   FROM mytable
                   WHERE [FileName] = 'History'
                   GROUP BY Member_Number
                   HAVING COUNT(*) > 1

Progress Update:

I got the below code so far but I am having to specify the actual member number. I guess I need to find a way to make the below code loop through all the member numbers. There are about 1,000+ member numbers that need to be updated in the table.

  SELECT *, t1.[FileName] + '_' + CAST(RN AS VARCHAR(50)) FROM
  (
  SELECT (ROW_NUMBER() OVER (ORDER BY(SELECT 1))) RN ,* FROM mytable WHERE Member_Number = 'BAY391'
  ) AS t1
  WHERE RN > 1


texts:
I am currently developing a simple book library system in ms access 2007 and need some help with the database design. I want to register books, borrowers and loans, so I assume that my database could consist of these three entities.

Borrower
Loan
Book

The relationships between the tables are as so:

Borrower has a 1 to many relationship with Loan.

Loan has a 1 to many relationship with Book.

The problem with this approach is that you can not create a row in the Book table without assigning a foreign key value; thus referencing the foreign key loanId of Book table with the related primary key in the Loan table. It breaks the referential integrity and it's not possible to create a book without relating it to a loaner.

So I think I need a join table between Book and Loan. This table should consist of the 2 primary keys from Book and Loan (a composite key). So there is a 1 to many relationship between Book and BookLoan and a 1 to many between Loan and BookLoan.

Is my way of thinking correct ? I can't figure out why I need the join/junction table between loan and book. Is there a more simple solution to my problem?

Thanks in advance :)

EDIT: I have added a E/R-diagram which has been corrected in respect to the answer, I have received.


texts: join 3 tables with one to many and one to one relationship

texts:
I have 3 tables:
    Memory_map:
        - Id
        - Memory_map_version
        - Product_id_hex
        - Parameter --------------------> want to add this to Histogram table

    Histogram_read:
        - Id
        - Memory_map_version -------> these 2 keys are referencing to the Memory_map table
        - Product_id_hex ---------> these 2 keys are referencing to the Memory_map table

    Histogram:
        - Id
        - Histogram_read_id

The relationship is:
Histogram_read (one) --> histogram(many)

Memory_map (one) --> histogram_read(one)

So Now I want to create a NEW Histogram table with the [Parameter] field from Memory_map table added to the original Histogram table.
Because Memory_map (one) --> histogram_read(one) is a ONE-TO-ONE relationship, so I will need to join these two tables first before I join the Histogram_read table with Memory_map table to add the [Parameter] field to Histogram table.
So, should I do a inner join first between Histogram_read and Histogram based on Histogram_read_id? (since Histogram_read_id is a key referencing the Histogram_read table records), but since it's a ONE-TO-MANY relationship, is it ok to do the INNER JOIN?
And Because I only want all records and columns from Histogram table - just with [Parameter] field added, so if I join the Histogram_read table with Histogram table before I join the Histogram_read table with the Memory_map table, how do I make sure the final table of Histogram wasn't messed up by columns in the Histogram_read table?
I have this following query but it doesn't work:
create view histogram_new
as
select M.parameter, Nest.* from memory_map M
RIGHT JOIN
(select * from histogram_read R
INNER JOIN histogram H
ON H.histogram_read_id = R.id) Nest
ON M.memory_map_version = Nest.memory_map_version
AND M.product_id_hex = Nest.product_id_hex;

I'm confused about the 3 tables join with one=to-many and one-to-one relationship, I am not sure what the logic should be. anyone can helps? THANKS!


texts: How to Load XML child nodes in to multiple tables

texts:
I would like to load the below XML into 2 tables

Table 1: Acct Table - it should have the ACCT_NUM and a Key to tie to the Table 2 below

Table 2:  Should have all the Contacts (1 record per Contact_Type) and should be able to link back to Table 1

Here is my XML:

<?xml version="1.0" encoding="UTF-8"?>
<Extract>
<Request>
<ACCT_NUM>111111</ACCT_NUM>
<Data>
<Contacts>
<Contact CONTACT_TYPE="CRM">
<CONTACT_TOLL_FREE>800.123.4444</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 11111</CONTACT_ADDRESS_LINE_1>
</Contact>
<Contact CONTACT_TYPE="Collections">
<CONTACT_TOLL_FREE>800.123.5555</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 22222</CONTACT_ADDRESS_LINE_1>
</Contact>
</Contacts>
</Request>
<Request>
<ACCT_NUM>222222</ACCT_NUM>
<Data>
<Contacts>
<Contact CONTACT_TYPE="CRM">
<CONTACT_TOLL_FREE>888.123.6666</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 33333</CONTACT_ADDRESS_LINE_1>
</Contact>
<Contact CONTACT_TYPE="Collections">
<CONTACT_TOLL_FREE>888.123.7777</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 44444</CONTACT_ADDRESS_LINE_1>
</Contact>
</Contacts>
</Request>
</Extract>

Any help is greatly appreciated.


texts: Create database fails with Microsoft SQL Server Error 5133, Operating syste, error 5 (Access is denied)

texts:
I just installed SQL server 2012 on Windows 2012 today. After the install, I changed the database settings to create database files in f:\mssql instead of the default. i verified the confired and running values.

Create database fails to create the .mdf and .log files with the following error -
Directory lookup for the file "f:\msql\data\test.mdf' failed with os error 5. (Access is denied).

This error occurs when I run sql server service as domain account. The domain account is an administrator and has full control on volume F.

If I run the sql server service as local system, I am able to create the database with mdf files on f:\ drive.

My sysadmin confirms and I have verified that the domain account has full control on f:

I look forward to your suggestions.


texts: How to improve performance of a query that joins three tables?

texts:
I want to fetch data from three tables: table A, table B and table C.

Table A in databaseA has the customerid column which is a primary key
Tables B and C in databaseB also have same column customerid (not primary key).

I want to retrieve the last transaction date in table A and table B for each customer in table A.

Expected result should have these columns:

customerID
Max(date in table B)
Max(date in table C)

The query below takes forever to run. How do I optimize the query to get desired result in less than 5 sec?

NB: tableA and tableB have about 10 million data each and both databases have the same character set.

select
   a.customerid,
   a.custname as name,
   max(b.lastdate) as lastdt,
   max(c.lastdate) as lastdtc,

     case
        when  max(b.lastdate) < date_sub(curdate(), interval 7 day)
          and max(c.lastdate) > date_sub(curdate(), interval 7 day)
                  then 'INACTIVE'
        when  max(c.lastdate) < date_sub(curdate(), interval 7 day)
         and  max(b.lastdate) < date_sub(curdate(), interval 7 day)
                  then 'DORMANT'
        when  max(c.lastdate) is null
         and  max(b.lastdate) < date_sub(curdate(), interval 7 day)
                  then 'DORMANT'
        else
                       'ACTIVE'
        end as Status
  from
           database1.table a
     inner join
           database2.table b
           on a.customerid=b.customerid
        left join
          database2.table c
           on a.customerid=c.customerid
           where a.customername like concat('$fromclient','%')
    group by a.customerid
         order by lastdt
    limit $fromclient offset $fromclient


texts: Verify if the client is connecting to the Oracle database using native network encryption

texts:
My environment is as below -

Server: Oracle 12C 12.1.0.2.v17 ( AWS RDS Service)

Client: Windows 2012 machine with Oracle 19C 64 bit full client

Tools : SQL Developer and Toad on the client machine

I have configured native network encryption for the RDS service by following the instructions in https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/use-network-encryption-and-integrity.html

I set following options on the RDS -

SQLNET.ENCRYPTION_SERVER= Accepted

SQLNET.ENCRYPTION_TYPES_SERVER= AES256

The client Oracle 19c 64 bit home is
C:\oracle\product\19.0.0\client_1\network\admin\sqlnet.ora

Both client tools using the TNS name in the above path; I verified this by using  tnsping

The sqlnet.ora in client has following options -

SQLNET.ENCRYPTION_CLIENT=REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

After the above configurations, I restarted my windows machine(just to make sure) and am able to establish connection to the RDS with Toad and SQL Developer tool.

MY question: How do I verify if my connection is encrypted ?

I tried executing below query -

select NETWORK_SERVICE_BANNER
from v$session_connect_info
where SID = sys_context('USERENV','SID');

The output I am seeing is

TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production

Encryption service for Linux: Version 12.1.0.2.0 - Production

Crypto-checksumming service for Linux: Version 12.1.0.2.0 - Production

SHA1 Crypto-checksumming service adapter for Linux: Version 12.1.0.2.0 - Production

I do not see "AES256 Encryption" in the NETWORK_SERVICE_BANNER output.

Related question: I need all the client connections from this machine to my server use encryption. Is there a way I can enforce this by using logon trigger ?


texts:
I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. And after a while it says that maximum timeout is over.
Shouldn't it be faster? I'm using InnoDB

INSERT INTO ip2nation (ip, country) VALUES
 (687865856, 'za'),
 (689963008, 'eg'),
 (691011584, 'za'),
 (691617792, 'zw'),
 (691621888, 'lr'),
 (691625984, 'ke'),
 (691630080, 'za'),
 (691631104, 'gh'),
 (691632128, 'ng'),
 (691633152, 'zw'),
 (691634176, 'za'),
 (691650560, 'gh'),
...


texts: SQLcl: set color in sqlprompt (ANSI/VT100 Control sequences)

texts:
This is my login.sql

SET sqlprompt "_user'@'_connect_identifier > "
SET sqlformat ansiconsole
SET serveroutput on
SET lines 3000

How to set the sqlprompt to color red for Prod (and green color for testing)?


texts: All databases restoring in Suspect Mode in SQL Server 2012

texts:
I've just re-installed SQL Server 2012 (removed the evaluation copy and installed a licenced version) and now every database I try to restore, it fails to completely restore and the database sits in Supsect mode. I've not come across this before and am not sure why it is happening.

SQL Server 2012, SQL Server Management Studio 2012


texts: COPY Command spawning multiple processes

texts:
I have a lengthy data pipeline that imports a CSV, creates a sequence of dependent views and then exports the results using COPY as a CSV. This process had been working fine, completing in around 30-60 seconds. Suddenly, this process has started running much slower when it reaches the copy command. Running SELECT usename, state, query FROM pg_stat_activity; shows three identical, active COPY commands under my username. The process does eventually complete but now takes up to 20 minutes or more. Other than changing some initial sub-setting, the data has remained unchanged. No one else is using this database but there are other users on the cluster.

Has anyone encountered this behavior before? Does anyone know what might cause a sudden slow down in a COPY operation?

Postgres 11.6, RHEL 7


texts: Slow Trigger performance when big batches

texts:
I have an update trigger that inserts into auditing tables.  We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when developing).
Now facing reality, I need to find a solution...

I've been doing many tests and researches to try to figure out how to solve my issue of having a trigger perform poorly...
I've come to the conclusion that to minimize the bad performance of the "Table Insert" in the execution plan, I need to insert in smaller batches.

The question is:  Since I'm not sure of where all the different updates can come from, I'm trying to figure out how I can insert the auditing records in batches within the trigger?

example, The update of the main table for 1 million records would happen and call the trigger, which would insert 100 thousand records at a time in some type of loop.

Is this possible? If so, how do you suggest?
If not, how else can I improve the table insert of the execution plan?

Addition of test scripts to reproduce:

This is a simplified version of the real thing

-- drop trigger PriceHist_trig_U
-- drop table MyPriceTable
-- drop table price_history
Create Table MyPriceTable (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100)
CONSTRAINT [PRICE_TAB_P01] PRIMARY KEY CLUSTERED
(
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Declare @Id int
Set @Id = 1

While @Id <= 1000000
Begin
   insert into MyPriceTable values (right('000000000000' + CAST(@Id as nvarchar(10)),13),'Grp ' + CAST(@Id%10 as nvarchar(10)), @id%3, RAND()*(25-10)+10, 1, 'there are many other columns')
   Print @Id
   Set @Id = @Id + 1
End

-- Drop table   price_history
create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max))
CREATE NONCLUSTERED INDEX price_history_nc1 ON price_history
(
    HistoryDate ASC,
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)

go
Create TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE
AS
INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate, ChangedColumns)
            SELECT INS.SKU,INS.PriceGroup,INS.PriceLevel,INS.Price,INS.Qty,INS.ManyOtherColumns, getdate(),
CASE WHEN update(Price) and INS.Price<>DEL.Price THEN 'Price-' ELSE '' END +
CASE WHEN update(Qty) and INS.Qty<>DEL.Qty THEN 'Qty-' ELSE '' END +
CASE WHEN update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns THEN 'other-' ELSE '' END
FROM INSERTED INS
JOIN DELETED DEL ON DEL.sku=INS.sku AND DEL.PriceGroup=INS.PriceGroup AND DEL.PriceLevel=INS.PriceLevel
WHERE  (update(Price) and INS.Price<>DEL.Price)
    OR (update(Qty) and INS.Qty<>DEL.Qty)
    OR (update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns)

/* tests */
update MyPriceTable set price = price-1

When I run this with the trigger disabled, it runs in 2 seconds.
When the Trigger is enabled, it took 32 seconds to complete.
The Execution Plan shows 98% on the "Table Insert"

I've been trying to figure out how to improve the table insert, but can't find anything concrete...

I've tried with a Clustered index and the performance is worse.

Any help would be appreciated


texts:
I'm using MySQL 5.7 and I want to set up SSL to force the client to use a cert.
This cert is a self-signed cert that I generate.
I have the cert for both server and client being:
- CA
- cert
- key

Where the CA has the cert for server and client.
I then configured the config file (mysqld.conf) to use the cert:

[mysqld]
ssl
ssl-ca=/database/mysql/ssl/ca-cert.pem
ssl-cert=/database/mysql/ssl/master-public.pem
ssl-key=/database/mysql/ssl/master-private.pem

And run the command to enforce it:

GRANT ALL PRIVILEGES ON `database` TO 'user'@'192.168.10.10' IDENTIFIED BY 'password' REQUIRE SSL;

I can see SSL is enabled and it's picking up the right files running the command:

SHOW VARIABLES LIKE '%ssl%';

However, from the client machine (192.168.10.10. in this example) I can connect via command line without specifying any cert and it just connects.
I can see the connection is using SSL with:

mysql> \s
SSL:            Cipher in use is DHE-RSA-AES256-SHA

But the idea is to allow t he connection only if I use the certs.
Is there a way to enforce it to make sure it won't connect without specifying the cert ?
So I can configure the application to use it and make sure other applications wouldn't access it.


texts:
EDIT - Putting my whole stored procedure:

I have the below stored procedure:

CREATE PROCEDURE [dbo].[data_Update]
  @Owner NVARCHAR(255),
  @ID VARCHAR(255),
  @Division VARCHAR(255),
  @Manager VARCHAR(255),
  @TeamMembers VARCHAR(255)
  AS
  BEGIN
    DECLARE @OwnerName VARCHAR(255),
    @ManagerName VARCHAR(255),
    @Country VARCHAR(255),
    @TEMP VARCHAR(255),
    @Vendor1 VARCHAR(100),
    @Vendor2 VARCHAR(100),
    @Vendor3 VARCHAR(100),
    @INDEX INT,
    @Division1 INT,
    @Division2 INT

    SELECT @Manager = NAME, @Country = Country FROM Users WHERE EMAILID = @Manager
    SELECT @Owner = NAME FROM Users WHERE EMAILID = @Owner

    IF(@Division IS NOT NULL)
    BEGIN
        SELECT @INDEX = CHARINDEX('-', @Division)
        IF(@INDEX > 0)
        BEGIN
            SELECT @TEMP = SUBSTRING(@Division, 1, @INDEX - 1)
            SELECT @Division1 = ID FROM DivisionOne WHERE Value = @TEMP
        END
        IF(@INDEX = 0)
            SELECT @Division1 = ID FROM DivisionOne WHERE Value = @Division
        IF(@INDEX > 0)
        BEGIN
            SELECT @TEMP = SUBSTRING(@Division, @INDEX + 1, LEN(@Division))

            SELECT @Division2 = ID FROM DivisionTwo
            WHERE Value = @TEMP AND Division1 = @Division1
        END
    END

    DECLARE @MemberEmails TABLE(col VARCHAR(255))
    DECLARE @MemberNames TABLE(col VARCHAR(255))
    DECLARE @Names VARCHAR(255)

    INSERT INTO @MemberEmails
    SELECT * FROM [dbo].[fnSplitString] (@TeamMembers, ',')

    INSERT INTO @MemberNames
    SELECT u.NAME FROM @MemberEmails e LEFT OUTER JOIN Users u ON e.col = u.EMAILID

    SELECT @Names =
       STUFF((SELECT '; ' + RTRIM(CONVERT(CHAR(50),col))
       FROM   @MemberNames b
       FOR XML PATH('')),1,1,'')

    DECLARE @Vendors TABLE(col varchar(50))
    DECLARE @itm1 int, @itm2 int, @itm3 int,
            @Vids varchar(50)
    INSERT INTO @Vendors
      SELECT VendorID from VendorMapping where ID = @ID
    SELECT @Vids = STUFF((SELECT '; ' + col
                FROM   @Vendors
                FOR XML PATH('')),1,1,'')
    SELECT @itm1 = X.value('x[1]', 'int'),
           @itm2 = X.value('x[2]', 'int'),
           @itm3 = X.value('x[3]', 'int')
      from (select cast('<x>'+replace(@Vids, ';', '</x><x>')+'</x>' as xml)) as T(X)

    SELECT @Vendor1 = Name FROM Vendors WHERE Id = @itm1
    SELECT @Vendor2 = Name FROM Vendors WHERE Id = @itm2
    SELECT @Vendor3 = Name FROM Vendors WHERE Id = @itm3

    -- Populate tblDocuments
    DECLARE @Docs VARCHAR(MAX)
    DECLARE @pos INT
    DECLARE @len INT
    DECLARE @type VARCHAR(100)
    DECLARE @value VARCHAR(8000)
    DECLARE @name VARCHAR(1000)
    SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID
    SET @Docs = @Docs + '$'
    SET @pos = 0
    SET @len = 0

    WHILE CHARINDEX('$', @Docs, @pos + 1)>0
    BEGIN
      SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
      SET @value = SUBSTRING(@Docs, @pos, @len)

      IF CHARINDEX('|', @value) >0
      BEGIN
        SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),
                @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)
        INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)
      END

      SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1
    END

    --- update table
    UPDATE tblProjects SET OwnerName = @Owner, OwnerCountry = @Country,
    ManagerName = @Manager, Division1ID = @Division1,
    Division2ID = @Division2, Team = @Names, Vendor1 = @Vendor1,
    Vendor2 = @Vendor2, Vendor3 = @Vendor3 WHERE ID = @ID
  END

I am running this for every row in tblProjects, inside an SSIS job.
Currently this step takes a lot of time to complete, for the whole set of records.
Anything that can even slightly speed up the queries is appreciated.

I have added a SET NOCOUNT ON; in the procedure, and it seems to have given a slight performance boost.


texts:
I made a backup and according to Percona it completed successfully.  Now I'm trying to prepare it and having the following issue:

./xtrabackup_55  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/backup/unprepared/2013_05_21_21_31_41_full
xtrabackup: cd to /backup/unprepared/2013_05_21_21_31_41_full
xtrabackup: This target seems to be not prepared yet.
xtrabackup: No valid checkpoint found.
xtrabackup: Error: xtrabackup_init_temp_log() failed.

I've been searching for answers for quite a while and not finding anything conclusive.  Any advice on how to resolve this?

Edit: per comment below, here is requested info:

xtrabackup_55 version 2.0.3 for Percona Server 5.5.16 Linux (x86_64) (revision id: 470)

cat /etc/my.cnf|grep innodb
innodb_data_home_dir = /data/mysql_data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/mysql/data/
innodb_file_per_table
innodb_buffer_pool_size = 5G
innodb_additional_mem_pool_size = 40M
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M
innodb_log_block_size = 512
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 8
innodb_doublewrite_file = /var/mysql/innodb_doublewrite.buff
innodb_flush_method = ALL_O_DIRECT