texts: How do I get column name from a SQL statement?

texts:
Is there any easy way just to get the column names returned from a SQL statement ?

Such as:

 SELECT *
 FROM ATable
 FULL OUTER JOIN BTable b ON a.SOME_KEY = b.SOME_KEY
 FULL OUTER JOIN CTable c ON b.SOME_ID = c.SOME_ID
 FULL OUTER JOIN DTable d ON a.SOME_OTHER_ID = d.SOME_OTHER_ID
 WHERE a.SOME_KEY = @key AND c.SOME_SERIAL_NR = @serialnumber

I cannot seem to get

select column_name
from information_schema.columns
where table_name = 'table'

to work in my current context


texts: SSIS Package Validation Error email task when attaching file created by previous task

texts:
While running in debug mode an SSIS package I get the folowing error.
"file ... does not exist ...",  but the file is created by a task earlier in the process, so most probably it will exist at runtime but not at validation. How to deal with this ?

Package Validation Error
------------------------------
ADDITIONAL INFORMATION:

Error at Send Zip file [Send Mail Task]: Either the file "..." does not exist or you do not have permissions to access the file.

Error at Send Zip file: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------


texts: Why is the database size larger than sum of table sizes

texts: I wanted to know why  database size is a way greater than thesum of table size in a database? How does it change?


texts: What problems are there in not using a primary key column at all?

texts:
What purpose would a primary key column serve on a table such as post_versions here? Nothing refers to it, and there are no queries where I will ever want to select a row by post_versions.id. It'll be joined to posts in most queries.

CREATE TABLE posts (
    id       SERIAL PRIMARY KEY,
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    user_id  INTEGER NOT NULL REFERENCES users ON DELETE RESTRICT
);

CREATE TABLE post_versions (
    id       SERIAL PRIMARY KEY, -- serves no purpose, could remove I think
    post_id  INTEGER NOT NULL REFERENCES posts ON DELETE CASCADE,
    updated  TIMESTAMPTZ NOT NULL DEFAULT now(),
    body     TEXT NOT NULL
);

Are there any problems if I just remove it?


texts: SSIS with MultiSubnet Availability Groups

texts:
I  am planning for a "multi-subnet" AG group . 2 at the primary data center in DC and one at San Jose. We are planning to test a vendor application which requires SSIS. I am planning for the SSIS to be in a separate instance and not on the AG, and if a failover was performed to SanJose, how do I configure SSIS to make sure it's available?

The application will be creating 3 databases, one of which I am planning to add to the AG. The renaming two will be on the SSIS server itself. Due to restrictions, I cannot install SSIS on the SQL Servers itself. This is one of the reasons I had to separate the SSIS into a standalone instance


texts: Will removing a foreign key constraint affect performance?

texts:
I have a units table and a reports table. A unit has many reports. So reports table has a foreign key unit_id which corresponds to the id of the unit. Hence, there is a foreign key constraint in reports table.

I began to realize that sometimes reports need to be created even if the unit doesn't exist for them yet. So when I tried to create a report without a unit, I obviously got this error:

 ERROR: insert or update on table "reports" violates foreign key constraint "reports_unit_id_fkey"
  Detail: Key (unit_id)=(7295) is not present in table "units".

My question is if I remove this foreign key constraint and enable reports to be created without a unit, will this affect performance when I try to get all reports associated with a unit?

SELECT * FROM reports WHERE unit_id=7295;

The only other option I have is to insert the reports into some orphans table, but then I would have to copy those orphans over when the unit is created, which is additional work.


texts: MySQL DBs taking up way too much disk space

texts:
I'm on Ubuntu Server 18.04 running MySQL server 5.7.27 with InnoDB. I confirmed that InnoDB file-per-table is On.
I have a mysqldump containing all of my DBs that takes up about 1.3 GB disk space.

After I imported the backup with sudo mysql -u root < myfile.sql, I noticed that there's significantly less space left on my drive. I checked the disk space usage of the newly created DBs in /var/lib/mysql and their combined size is about 14 GB.

I ran sudo mysqlcheck -o --all-databases -u root to optimize the tables, but it only reduced the size to 11 GB:

root@ip-x-x-x-x:/var/lib/mysql# du -sm * | sort -nr
1468    dbvwgcayak57wx
1468    dbmuj7j8scnzt6
1468    dbmmqtf5sss9pk
1468    dbf9ynyscjzw83
1468    dbdr5hk7kvsh73
1468    db3mj8b7b5ezuw
1468    db22z4jcfbf9yn
544 db7b5ezuwb8ca4
76  ibdata1
48  ib_logfile1
48  ib_logfile0
35  dbbz9e6hcmqbtb
23  db688zwgk7uvdh
19  mysql
12  ibtmp1
3   phpmyadmin
2   performance_schema
1   sys
1   ib_buffer_pool
1   dbzqcfb9scuv3h
1   dbscjzw83rv253
1   auto.cnf
0   debian-5.7.flag

All my large DBs contain several hundred tables that are small in size (less than 2 MB according to phpmyadmin), but they take up a lot more actual disk space (about 8 MB).

What else can I do to reduce disk space?


texts: SQL query for all combinations without repetition

texts:
I need a function for calculating combinations without repetition from a list.

Example:
I have a list of names e.g.

Tom
Bob
Paul
Tina

Now I need a list of 3 names out of the 4 without having a name twice and without having the 3 names twice because of ordering. The correct result would be

Tom,Bob,Paul
Tom,Paul,Tina
Tom,Bob,Tina
Bob,Paul,Tina

Of course this also should work quite fast with 14 out of 19 or similar.


texts: Should I convert the frontend generated UUIDv6 to binary(16) for use in SQL Server as the clustered primary key?

texts:
Background
As suggested by the front-end developer, I looked into using UUID as the primary key for a bunch of tables in our new system. From learning the pros and cons of random vs. sequential UUIDs, to the use of a non-clustered primary key in combination of a clustered index with a sort-able type, my research pointed me to UUIDv6, and an implementation of it.
It is able to generate UUIDs like the below (that is sequential):

UUIDv1                               UUIDv6
------------------------------------ ------------------------------------
5714f720-1268-11e7-a24b-96d95aa38c32 1e712685-714f-6720-a23a-c90103f70be6
68f820c0-1268-11e7-a24b-96d95aa38c32 1e712686-8f82-60c0-ac07-7d6641ed230d
7ada38f0-1268-11e7-a24b-96d95aa38c32 1e712687-ada3-68f0-93f8-c1ebf8e6fc8c
8cc06fd0-1268-11e7-a24b-96d95aa38c32 1e712688-cc06-6fd0-a828-671acd892c6a
9ea6a6b0-1268-11e7-a24b-96d95aa38c32 1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which I thought SQL Server would gladly sort them for me in the clustered primary key (uniqueidentifier) column.
Little did I know how SQL Server would sort an uniqueidentifier column. Here's the ascending sort result:

UUIDv6 uniqueidentifier sorted
------------------------------------
1e712688-cc06-6fd0-a828-671acd892c6a
1e712686-8f82-60c0-ac07-7d6641ed230d
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c
1e712685-714f-6720-a23a-c90103f70be6
1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which is causing fragmentation as if using random UUIDs. This post explains how they were actually sorted.
The real question
Luckily, the system is still in development. Which of these options should I go for next?

reorder the bytes so that the most/least significant bytes are where SQL Server expects them to be

UUIDv6                               UUIDv6 reordered bytes
------------------------------------ ------------------------------------
1e712685-714f-6720-a23a-c90103f70be6 c90103f7-0be6-a23a-6720-1e712685714f
1e712686-8f82-60c0-ac07-7d6641ed230d 7d6641ed-230d-ac07-60c0-1e7126868f82
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c c1ebf8e6-fc8c-93f8-68f0-1e712687ada3
1e712688-cc06-6fd0-a828-671acd892c6a 671acd89-2c6a-a828-6fd0-1e712688cc06
1e712689-ea6a-66b0-910c-dbcdb07df7a4 dbcdb07d-f7a4-910c-66b0-1e712689ea6a

convert the UUIDv6 to binary(16) and use that instead

UUIDv6                               UUIDv6 binary(16)
------------------------------------ --------------------------------
1e712685-714f-6720-a23a-c90103f70be6 1e712685714f6720a23ac90103f70be6
1e712686-8f82-60c0-ac07-7d6641ed230d 1e7126868f8260c0ac077d6641ed230d
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c 1e712687ada368f093f8c1ebf8e6fc8c
1e712688-cc06-6fd0-a828-671acd892c6a 1e712688cc066fd0a828671acd892c6a
1e712689-ea6a-66b0-910c-dbcdb07df7a4 1e712689ea6a66b0910cdbcdb07df7a4

Problem with option 1
The UUID standard embeds a 4-bit version field inside the ID. UUIDv6 (still non-standard) also follows that rule. The way I will reorder them is going to break this.
Problem with option 2
I'm not sure. Can hardly find anyone talking about it except this, which is going against the idea. Are there other pitfalls that I should be aware of in using the binary(16) type?
Thanks!


texts: Why do wildcards in GROUP BY statements not work?

texts:
I am trying to make the following SQL statement work, but I get a syntax error:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*

Here, A is a wide table with 40 columns and I would like to avoid listing each column name in the GROUP BY clause if possible. I have many such tables over which I have to run a similar query, so I will have to write a Stored Procedure. What's the best way to approach this?

I am using MS SQL Server 2008.


texts: Insert into ... (...) (Select...) on duplicate key update

texts:
Can you help me to solve a problem. The task is to insert a record in MySQL database.
terms:

If the record exists - to update the record.
If the record does not exist - to create a new one.

The Key columns in the table 'NETFLOW'.'Month' are:

'Year'
'Month'
'Day'
'Hour'
'srcaddr'
'dstaddr'.

The thing is query as quickly as possible and if possible in one query.

The text of the current query is:

    INSERT INTO `netflow`.`Month` (`Year`, `Month`, `Day`, `Hour`, `srcaddr`,`dstaddr`, `doctets`)
    (
      SELECT
        YEAR( FROM_UNIXTIME(  `unix_secs` ) ) AS `YEAR`,
        MONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `MONTH`,
        DAYOFMONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `DAY`,
        HOUR( FROM_UNIXTIME(  `unix_secs` ) ) AS `HOUR`,
        `srcaddr` ,
        `dstaddr` ,
        `doctets`
      FROM  `netflow`.`raw`
        WHERE `unix_secs` <= 1394179200
      GROUP BY
        `YEAR`,
        `MONTH`,
        `DAY`,
        `HOUR`,
        `srcaddr`,
        `dstaddr`
    )
    ON DUPLICATE KEY UPDATE

...


texts: How I develop Union Report Views for Multiple Single Tenant Databases?

texts:
We have a database for each customer, over 20+ databases. All database schemas  are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How would I dynamically generate views for tables in the database, which combine the data? The databases reside on the same server instance.

Many db professionals prefer single tenant databases.

Single Tenant Database Discussion


texts: Stored Procedure is not returning complete result

texts:
I am using the query as mentioned in the answer accepted in the question located at Stored Procedure doesn't show result in SQL Server

Now when I run this SP with the values provided in the parameter, the SP shows results only of specific parameter values when returning the result. Can there be any chance of values getting terminated? The parameter values are multiple and comma seperated.

Below is the SP;

    ALTER PROCEDURE [dbo].[sp_tst_JulieExportCSENGOnly]
    @ProjectHierarchy NVARCHAR(max),
    @ProjectHomeGroup NVARCHAR(max),
    @ProjName NVARCHAR(max),
    @ProjOwn NVARCHAR(max),
    @TimeByDay DATETIME,
    @TimeByDay2 DATETIME
AS
   SET NOCOUNT ON

   BEGIN

   DECLARE @ProjectHierarchyTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjectHomeGroupTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjNameTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjOwnTbl TABLE ( Value NVARCHAR(MAX))

    INSERT  INTO @ProjectHierarchyTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjectHierarchy) ;

   INSERT  INTO @ProjectHomeGroupTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjectHomeGroup) ;

    INSERT  INTO @ProjNameTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjName) ;

    INSERT  INTO @ProjOwnTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjOwn) ;

   SELECT
  [IR.TimesheetProjectHoursByDay].[Project Departments]
  ,[IR.TimesheetProjectHoursByDay].ProjectName
  ,[IR.TimesheetProjectHoursByDay].ProjectOwnerName
  ,[IR.TimesheetProjectHoursByDay].[Project Hierarchy]
  ,[IR.TimesheetProjectHoursByDay].[Project Home Group]
  ,[IR.TimesheetProjectHoursByDay].[CSENG Project Type]
  ,[IR.TimesheetProjectHoursByDay].TaskName
  ,[IR.TimesheetProjectHoursByDay].[Capital Project]
  ,[IR.TimesheetProjectHoursByDay].[Capital Task]
  ,[IR.TimesheetProjectHoursByDay].TimeByDay
  ,[IR.TimesheetProjectHoursByDay].ResourceName
  ,[IR.TimesheetProjectHoursByDay].ActualWork
  ,[IR.TimesheetProjectHoursByDay].[Resource Home Group]
  ,[IR.TimesheetProjectHoursByDay].[Resource Cost Center]
  ,[ResourceStandardRate]
FROM
  [IR.TimesheetProjectHoursByDay]
WHERE
  [IR.TimesheetProjectHoursByDay].[Project Departments] LIKE N'CSENG'
  AND ActualWork > 0
  AND [IR.TimesheetProjectHoursByDay].[Project Hierarchy] IN (SELECT VALUE FROM @ProjectHierarchyTbl)
  AND [IR.TimesheetProjectHoursByDay].[Project Home Group] IN (SELECT VALUE FROM @ProjectHomeGroupTbl)
  AND ProjectName IN (SELECT VALUE FROM @ProjNameTbl)
  AND ProjectOwnerName IN (SELECT VALUE FROM @ProjOwnTbl)
  AND [IR.TimesheetProjectHoursByDay].TimeByDay >= @TimeByDay
  AND [IR.TimesheetProjectHoursByDay].TimeByDay <= @TimeByDay2

   END

Thanks!


texts: Carriage return/line feed stopped working in SQL Server

texts:
SQL was working fine last week with the old CHAR(13)+CHAR(10) for line feed/carriage return.

DECLARE @text varchar(2000)
SET @text =
'Attached is your new reporting ID and temporary password.'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ 'The new login/password will be updated  on ' + Convert(char(10), @ticketdate,101)

Last week this ran as expected giving me nicely formatted text with a space between the lines.

This week, the same code on the same server is returning one long run-on line of text.

This seems like this is a collation issue? Or something like that?

The code has worked as expect so something changed, but I can't identify what might have changed.

This seems like it could be collation issue? But it appears to be the default latin setting.


texts:
i am using Oracle 12c in debian Jessie
i want to import an old database exported with expdp on oracle 12c
when i try to launch the commande 'impdp' i got those errors :
'ORA-39002: invalid operation'
'ORA-39070: Unable to open the log file'
'ORA-39087: directory name DUMPS is invalid'
Can you help me please?


texts:
I'm starting to take on more of a DBA role and trying to get tooled up with scripts and knowledge to help with this.

I have been trying the sp_blizt procs in a dev environment. A key area I'm trying to focus on is understanding Waits.

My question is when you see a list of Waits how do you relate them back to a proc/query that is causing them?

For eg. When I see asyn_network_io, how can I show which client app is causing this and what is it running at the time to cause this?


texts: Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

texts:
I have something that looks like this (SQL Fiddle here):

Date        Location    Value
1/1/2012    Baltimore   36
1/1/2012    Houston     36
1/1/2012    Chicago     55
2/1/2012    Baltimore   49
2/1/2012    Houston     58
2/1/2012    Chicago     51
3/1/2012    Baltimore   11
3/1/2012    Houston     13
3/1/2012    Chicago     18

However, I want it to look like this:

Date        Baltimore   Houston     Chicago
1/1/2012    36          36          55
2/1/2012    49          58          51
3/1/2012    11          13          18

How do I group by the Date and create new columns for each distinct Location? I do not know the complete set of possible Locations. I'm working in Oracle 11g Release 2.


texts:
I have a MongoDB database installed and configured, but I have a very big problem regarding security.

I have enabled authentication and created a bunch of users, each with its own permissions, everything ok there, now my problem is that if a bad guy gets access to my server, he can simply restart the service without specifying the authentication parameter, and access all my databases just by logging in with the localhost.

In the documentation it says:

  The localhost exception applies only when there are no users created
  in the MongoDB instance.

Well, I have already created users, and I am still able to access my database using the localhost exception. Am I doing anything wrong? Or is the documentation wrong? If so, this seems to be a major security problem.

The Mongo version I am using is 3.4.

These are the steps I followed to enable authentication:

Joined Mongo without the --auth parameter
Created an Admin User

 use admin

 var user = {
   "user" : "Admin",
   "pwd" : "1234",
   roles : [
     {
       "role" : "root",
       "db" : "admin"
     }
   ]}

 db.createUser(user);

Restarted Mongo with the --auth parameter
Created a database and some collections

use TestDB
db.createCollection( "TestCollection1" );
db.createCollection( "TestCollection2" );

Created a custom role:

use TestDB

db.runCommand({ createRole:"TestUserRole",
  privileges : [
     { "resource": {"db" : "TestDB", "collection" : "TestCollection1"}, "actions": ["insert", "update"] },
     { "resource": {"db" : "TestDB", "collection" : ""}, "actions": ["insert"] }
   ],
   "roles" : [ { role: "read", db: "TestDB" } ]
 })

Created a regular user

use TestDB

var user = {
  "user" : "TestUser",
  "pwd" : "1234",
   roles : [
      {
          "role" : "TestUserRole",
          "db" : "TestDB"
      }
]}

db.createUser(user);

After this, I can access Mongo with both users, with the Admin account I can find, insert, update and delete in both collections of the database TestDB. With the user TestUser, I can only see the database TestDB (can't see the System collections) and can only find, insert and update TestCollection1, and can only find and insert in TestCollection2.

This is the behaviour I want, all working fine. Now, if I restart Mongo without the --auth parameter, I shouldn't be able to connect to Mongo using the localhost exception because users are already created. Yet I am able to connect.


texts: Restore Database with Multiple Restore commands

texts:
For automation, I have to dynamically construct the restore commands and thus I have created a script. A .bak file is created on a remote server and I receive it daily. The script builds the restore string and executes it.

Only hindrance so far is I'm unable to restore the db on multiple steps, because the back up file comes from an external source and fails with  "Directory lookup for the file "xxxxxx.ndf" failed with the operating system error 3 (The system cannot find the path specified.)." It is the folder where the backup resides on the remote server.

Basically the script looks like:

RESTORE DATABASE x FROM DISK = 'E:x.bak'  WITH MOVE 'a' TO 'E:\x.mdf', NORECOVERY
RESTORE DATABASE x FROM DISK = 'E:x.bak'  WITH MOVE 'b' TO 'E:\x1.ndf', NORECOVERY
RESTORE LOG x      FROM DISK = 'E:\x.bak' WITH MOVE 'c_log' TO 'E:\x.ldf',NORECOVERY
RESTORE DATABASE x WITH RECOVERY

How can I achieve such a scenario?


texts: SQL Server 2012 stop working after specific time

texts:
SQL Server 2012 stops working after every 6 to 7 hours, SQL Server Management Studio not connected, almost every service related to SQL Server stops, after system restart every service is working fine, I have got error in event window
   OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2).

SQL Server Agent log error

Sql server Log


texts: Percona Toolkit pt-archiver doesn't work with --bulk-insert option

texts:
I'm running pt-archiver like this:

pt-archiver --source ... --dest ..
--where "last_updated BETWEEN '2015-01-26 00:00:00' AND '2015-02-01 23:59:59'"
--ignore --no-check-columns --no-ascend
--limit=10 --progress=10

And everything seems to be working fine. However, when I add the --bulk-insert option to use LOAD DATA, everything seems fine and I can see queries running on the database, but no data is changed. It's like the tool is not committing a transaction or something like that.

I'm using MySQL 5.6 on Amazon RDS and Percona Toolkit 2.2.12.


texts: Make queries and BCP faster on a very large table

texts:
This question is somewhat of a continuation to this:
Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that question here for legibility.

I've written an application with a SQL Server backend that collects and stores an extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in the avenue of 3-4 billion per day (20 hours of operation).

My application receives data, chunks it into batches of ~100k records and creates BCP-ready staging files, moves them to the server and SQL Server uses BCP to move the data into a production table. With that solution I've successfully moved 5.2 billion rows into my production table with little to no backlog of BCP files. I have BCP running continually as a SQL Agent job.

My production table has the following schema:

CREATE TABLE [dbo].[SignalValues](
    [Timestamp] [datetime] NOT NULL,
    [SignalId] [uniqueidentifier] NOT NULL,
    [SignalValue] [nvarchar](4000) NULL,
    [SignalValueId] [uniqueidentifier] NOT NULL
)

Because of the volume of records, the table is partitioned on [Timestamp] every half hour from 2016-01-20 21:00:00.000 to 2016-06-15 08:00:00.000. (~11k total partitions).

My problem is twofold:

Querying the table in any way takes eons.
While queries are running against the table, the BCP processing basically stops. It's like all processing power goes to the query and I get a files backlog.

How do I speed up query performance? Users will mostly query based on [Timestamp] and SignalId. I created a non-clustered index on [Timestamp] but it didn't seem to speed things up that much. Seems to be that the more indexing I apply, the slower the BCP inserts which creates a file backlog. Any index suggestions?

My assumption of partitions is that internally they act as separate tables so if I were to query [Timestamp] for 2016-01-25 22:01:00.000, it should only lock the one partition it needs for the query and leave the other partitions unlocked. Is my assumption incorrect? It would seem so with the behavior seen in 2) above. How do I force SQL Server not to lock during a SELECT query (WITH (READPAST) and WITH (NOLOCK) didn't help)?

I should note that rows in this table will never be updated or deleted. Also, it's possible that data could be inserted from a time in the past, depending on what the applications providing my application with data set as the timestamp for their rows. This unfortunately negates any possibility of partition switching unless I were to do some crazy magic.

The average number of rows per partition is ~70 million.

Based on the feedback from my previous question (above), I was able to beef up my server considerably. The following is my server's system information:

Also, I've configured SQL Server as follows based on feedback:

All database-related files are connected to a SAN which is connected to the system via fibre channel
My database's data file is on its own drive alone -- a RAID 10 array of 4 spinning disks
tempdb consists of 6 files (half the number of logical processors), each sized at 512mb, all on one drive -- a RAID 0 array of 2 spinning disks
All transaction logs live on their own drive -- a RAID 1 array of 2 spinning disks.

The table does not have a clustered index. I'd had a clustered unique index (the PK) before but the uniqueness constraint caused inserts to take 10 minutes for 100k records in a table with 800m rows. My understanding was that a clustered index creates a uniqueness constraint internally per the heading, "Can you create a clustered index on a column with duplicate values?" from this page:

14 SQL Server Indexing Questions You Were Too Shy To Ask

I could be completely wrong though.

Let me know if I'm missing anything.


texts: Multiple-Question choice - TABLE for category of question?

texts:
I am working on a multiple choice question form. I am using Flask on the back-end and MySQL 5.7 as a database.

There will be more than 1 end-user. I already made a users table but haven't work on it yet
There are multiple questions but I only display one question at once.
Every question have either 3 or 4 possible choices (A, B, C or A, B, C, D)
There is always one correct answer
The user can filter question based on category and get stats by category (% of question answered for this category for example)

Should I create a new table called category which would look like category_id (int, primary_key), category_text (varchar(50))?
CREATE TABLE `questions` (
  `question_id` int NOT NULL AUTO_INCREMENT,
  `contributor_id` int NOT NULL,
  `question_text` varchar(1000) NOT NULL,
  `category` varchar(50) NOT NULL,
  `answer_a` varchar(200) NOT NULL,
  `answer_b` varchar(200) NOT NULL,
  `answer_c` varchar(200) NOT NULL,
  `answer_d` varchar(200) DEFAULT NULL,
  `correct_answer` varchar(20) NOT NULL,
  PRIMARY KEY (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


texts: Distribute data into x groups of at-most n records each

texts:
I have a set of Z entries(e.g. 100 records), which I want to insert into Header and Detail tables.

I want to divide this group into X groups, with each group containing at-most N entries. The last group may contain <= N entries. Here X is variable and N is static. BatchID is the IDENTITY key of the header table.

Simple Approach

a. dump all the data in Temp table
b. Find number of Loops : CEILING(COUNT/N)
c. For every loop insert 1 entry in Header, find IDENTITY KEY and use that as Batch ID and insert N records in Detail Table with that Batch ID like:

UPDATE TOP(N) #Temp
SET IsPickedUp = 1
OUTPUT @BatchID,deleted., deleted....
INTO Detail(BatchID, ...)
WHERE IsPickedUp = 0

Optimized Approach

a. Find number of batches to be created of N records, where last Batch Count will be <= N
b. Insert all records from temp table to Detail table with correct BatchID

I need help for the second approach.

Here is my existing Code that works fine but take 40 min in production. One crucial requirement is that either the batches Are created or it is not created, we don't want incomplete population (e.g. transaction in while loop)

BEGIN TRY
BEGIN TRAN
    CREATE TABLE #TotalCID (ConsumerID VARCHAR(16),IsPickedUp BIT NOT NULL CONSTRAINT DF_IsPickedUp DEFAULT (0))
    CREATE CLUSTERED INDEX  IX_ConsumerID  ON #TotalCID(IsPickedUp,ConsumerID)

    INSERT INTO #TotalCID (ConsumerID)
    SELECT ConsumerID FROM ConsumerTable WHERE SeedingStatus IN (0,2) -- 2Cr Records

    DECLARE @TotalCount DECIMAL(11,2) = 0
    DECLARE @BatchCount DECIMAL(9,2) = 0
    DECLARE @LoopCnt INT = 0
    DECLARE @BatchSize INT = 25000
    DECLARE @batchId INT = 0

    SELECT @TotalCount=COUNT(ConsumerID) FROM #TotalCID

    SET @LoopCnt = CEILING (@TotalCount/@BatchSize)

    WHILE (@LoopCnt > 0)
    BEGIN

        INSERT INTO ETLHeader(Batch_Date,Total_Rec_count)
        VALUES(GETDATE(),0)

        SET @batchId = SCOPE_IDENTITY() -- BatchID in ETLHeader is PK

        UPDATE TOP (CONVERT(INT,@BatchSize)) #TotalCID
        SET IsPickedUp=1
        OUTPUT @batchId,deleted.ConsumerID
        INTO ETLDetail(Batch_ID,ConsumerID)
        WHERE IsPickedUp = 0

        SET @BatchCount = @@rowcount

        UPDATE ETLHeader
        SET  Total_Rec_count = @BatchCount
        WHERE Batch_Id = @batchId

        SET @LoopCnt=@LoopCnt-1
    END

        UPDATE ConsumerTable
        SET SeedingStatus = 1
        WHERE ConsumerID IN (SELECT ConsumerID FROM #TotalCID Where IsPickedUp= 1)
        AND SeedingStatus IN (0,2)

IF OBJECT_ID('tempdb..#TotalCID') IS NOT NULL
    DROP TABLE #TotalCID
COMMIT TRAN
END TRY
BEGIN CATCH
-- ERROR and Open TRAN Handling
END CATCH


texts:
I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT
    DECODE(
        TO_CHAR(MIN(DATE), 'YYYYMMDD')
        , ''
        , NULL
        , TO_DATE(TO_CHAR(MIN(DATE), 'YYYYMMDD'), 'YYYYMMDD')
    )
FROM TABLE
WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE(TO_CHAR(MIN(DATE), 'YYYYMMDD'), 'YYYYMMDD')
FROM TABLE
WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified.  Can someone shed some light on this?  This is a problem since the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: The two conversions (from date to char and then again to date) here is due to the fact that the query as I put it here is really two different queries: the first one inserts a DATE in an auxiliary table's column which has a CHAR format; then another query takes this data to the final table, where it's stored as a DATE, and hence the second conversion.  I simplified it here to make it more understandable. The DECODE is necessary as some of the rows don't have data on the date column.

Anyway, I think the best solution is to do ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD' as suggested by @Phil and @LeighRiffel, and then both queries (with and without DECODE) return the same date.  Another possible workaround would be to leave the final data as CHAR instead of converting it to DATE, but this would have a large impact on the application I'm working on, so I'm sticking to the ALTER SESSION ... way.


texts: Query FROM Indexed View queries underlying table in Execution plan

texts:
I have a largish table (>126 million rows), and a need to optimize a specialized set of queries, so I created an Indexed view on the table, and coded a procedure that would select from the Indexed View when the conditions were favorable for it.

Execution of the procedure took longer than I expected, so I looked at the execution plan, and I see that the query that takes the longest is the one that selects from my view, and when I hover over the element that takes the longest it's a Clustered Index Seek of the CI of the underlying table.  Not the Index on the view.

I understood that an Indexed View was a sort of virtual table that was stored on disk, and that when queried, did not pass the query through to the underlying table at all.   Is this not the case?


texts: Selecting from an excel spreadsheet into SQL Server table

texts:
This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express).

So for the following code snippet:

Declare @strSQL as varchar(200)
declare @file as varchar(200)

SET @file='C:\temp\file.xlsx'
SET @strSQL=N'SELECT * INTO #mytemptable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database='+@file+';HDR=YES'', ''SELECT * FROM [Sheet1$]'');'
SET @strSQL=@strSQL+N'SELECT * FROM ##mytemptable'
PRINT @strSQL
Exec (@strSQL)

EXPANDED SQL STATEMENT

SELECT *
INTO #mytemptable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\temp\file.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]');

SELECT * FROM ##mytemptable

I get this error:

  Msg 7399, Level 16, State 1, Line 1
  The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
  Msg 7303, Level 16, State 1, Line 1
  Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


texts:
Mongdb start up warning logging, I see lot questions around these warning in this forum however I wanted to know where does these warnings are logged. I could find them in mongod.log.
Can someone share path for these warning logs ??
MongoDB server version: 3.4.1
Server has startup warnings:
2020-11-19T19:36:51.030-0800 I STORAGE  [initandlisten]
2020-11-19T19:36:51.030-0800 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2020-11-19T19:36:51.030-0800 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] ** WARNING: The server is started with the web server interface and access control.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] **          The web interfaces (rest, httpinterface and/or jsonp) are insecure
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] **          and should be disabled unless required for backward compatibility.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. rlimits set to 128538 processes, 500000 files. Number of processes should be at least 250000 : 0.5 times number of files.
2020-11-19T19:44:38.433-0800 I CONTROL  [initandlisten]
mongo7052:SECONDARY>

MongoDB server version: 3.4.1
Server has startup warnings:
2021-02-19T00:04:24.384-0800 I CONTROL  [main] ** WARNING: --rest is specified without --httpinterface,
2021-02-19T00:04:24.384-0800 I CONTROL  [main] **          enabling http interface
2021-02-19T00:04:24.469-0800 I STORAGE  [initandlisten]
2021-02-19T00:04:24.469-0800 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2021-02-19T00:04:24.469-0800 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten]
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten] ** WARNING: The server is started with the web server interface and access control.
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten] **          The web interfaces (rest, httpinterface and/or jsonp) are insecure
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten] **          and should be disabled unless required for backward compatibility.
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2021-02-19T00:04:27.390-0800 I CONTROL  [initandlisten]
2021-02-19T00:04:27.391-0800 I CONTROL  [initandlisten]
2021-02-19T00:04:27.391-0800 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2021-02-19T00:04:27.391-0800 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2021-02-19T00:04:27.391-0800 I CONTROL  [initandlisten]


texts: MYSQL Update data based on top 10 records of each user

texts:
I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of user_score in user_scores table. I have tried following two queries but not successful with these.

1.

UPDATE      user
SET         user.user_score = (
    SELECT AVG(score) as avg_score FROM (
        SELECT score FROM user_scores WHERE user_id = user.id ORDER BY id DESC LIMIT 0,10)as x)

I am facing error Unknown column 'user.id' in 'where clause' cuz I am not able to pass variable to subquery of the query

Other thing I have tried by JOIN but in JOIN I am not able to calculate AVG(score) of each user based on latest 10 records.

Please suggest.


texts: Best way to move sybase IQ table to Sybase ASE

texts: I use bcp to copy Sybase IQ table to sybase ASE server. Actually bcp first creates some bulk file with extracdet sybase IQ table there, and after just loads that file to Sybase ASE. Is it the one and the best (considering accuracy, speed) way to do that?


texts:
As I know there is only 32bit version of 10g XE, am I right? And the installation file is called "oracle-xe-univ-10.2.0.1-1.0.i386.rpm".

I want to install it on Centos 6.4 64bit. But during the installation it shows me the following error message:

error: unpacking of archive failed on file /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/kgpmon;528b4cca: cpio: read

Note:

I had 10g XE database on 32bit server.

I was taking daily backups.

Now I just have 10g XE backup but the server where this database existed is broken and my sysadmins installed me Centos 6.4 64bit on another server.

I installed Oracle 11g XE 64bit on new server but cann't upgrade database because upgrading requires pre-upgrade steps that must be done on 10g XE database , re-backup it, transfer backup to new server, restore , startup upgrade, run post-upgrade steps..

If there is a way to restore 10g XE backup on 11g XE and upgrade it without using 10g XE database anymore.


texts: passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb

texts:
I am often in need to run the sp_executesql in the context of different databases

I would like to add the same parameters as the sp_executesql into the sp_ineachdb

But when I look at this:
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

  sp_executesql [ @stmt = ] statement   [      { , [ @params = ]
  N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
       { , [ @param1 = ] 'value1' [ ,...n ] }   ]

I can't find out how to define all the parameters to add to the sp_ineachdb.

For example the code below checks for an index inside a database:

    declare @IndexName NVARCHAR(128)='si_xdes_id'
    declare @dbname NVARCHAR(128) = db_name()
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @retval INT

IF @IndexName IS NOT NULL /*validate the index exists in the @dbname database*/
   BEGIN

        SELECT @sql = 'SELECT @retvalOUT = CASE WHEN EXISTS (
                               SELECT * FROM ' + @dbname + '.sys.indexes I WHERE I.Name = @IndexName  )
                               THEN 1 ELSE 0 END '

        SET @ParamDefinition = N'@retvalOUT int OUTPUT,
                                @IndexName SYSNAME';

        EXEC sp_executesql @SQL, @ParamDefinition, @retvalOUT= @retval OUTPUT, @IndexName  = @IndexName;

        IF (@retval = 0)
            BEGIN

               --RAISERROR('The index called %s does not exist on database %s',16,1,@IndexName,@dbname)
               RETURN

            END
   END

for me to use it in conjunction with sp_ineachdb I have to wrap the whole thing into dynamic sql:

    DECLARE @sql NVARCHAR(MAX)=

'   declare @IndexName NVARCHAR(128)=''uc_emailAddress''
    declare @dbname NVARCHAR(128) = db_name()
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @retval INT

IF @IndexName IS NOT NULL /*validate the index exists in the @dbname database*/
   BEGIN

        SELECT @sql = ''SELECT @retvalOUT = CASE WHEN EXISTS (
                               SELECT * FROM '' + @dbname + ''.sys.indexes I WHERE I.Name = @IndexName  )
                               THEN 1 ELSE 0 END ''

        SET @ParamDefinition = N''@retvalOUT int OUTPUT,
                                @IndexName SYSNAME'';

        EXEC sp_executesql @SQL, @ParamDefinition, @retvalOUT= @retval OUTPUT, @IndexName  = @IndexName;

        select @dbname,@retval

        IF (@retval = 0)
            BEGIN

               --RAISERROR(''The index called %s does not exist on database %s'',16,1,@IndexName,@dbname)
               RETURN

            END
   END
'

if object_id('tempdb..#radhe') is not null
   drop table #radhe
create table #radhe(dbname sysname, the_index_exist_here int)
insert into #radhe
EXEC sp_ineachdb @command = @sql, @user_only = 1;

select * from #radhe

which gives me:

question:

is there any easier way to get this done?

I would like to pass to sp_ineachdb  all the parameters that I currently pass to sp_executesql and just gracefully collect the output.

Is that any possible?


texts: Contained User within a Non-Contained Azure SQL Database

texts:
While granting access to Azure SQL databases I have been creating Contained Users directly in the SQL database like so:-

CREATE USER [email@domain.com] FROM EXTERNAL PROVIDER;

As this has been working fine, and users need to add the database name to the connection, I thought azure would know this is now a contained database.

However, upon checking, I've found the database is not contained

select containment, containment_desc from sys.databases

containment /   containment_desc
0           /   NONE
0           /   NONE

Should I now convert the database to be contained?
Are there any implications for leaving the database in this state?
Should I revert back to creating the user at both the server and DB level?


texts: SQL trigger when a row is updated question

texts:
My problem is as follows.

I an creating a MySQL database for a company that whenever a row is updated on the DB i want to have a trigger that calls a cURL request to transfer the updated information to our clients.

Is doing this the best way to do this? or is it better to do this in the php script that transfers the data to us?

Thank you for your time and experience.


texts: SQL Server 2016 Express restore size limit

texts: SQL Server 2016 Express indicates that it will accept a database of 10GB. I try to do a restore with overwrite of existing blank database with a 8.037GB .bak file and get the message that the file is too big to restore. Any suggestions?


texts: Should I join all of my polygons to make a single polygon for point-in-polygon search?

texts:
I have a set of of polygons. Sometimes they are completely disconnected (like separate patches). Other times, two sides of two polygons may touch each other (when the polygons are adjacent like on a chess board). The polygons never cross each other. In other words, their intersection is always empty.

I need to check if a point is contained in any one of those polygons. Is there a way I can build a single geometry using these polygons and check if the point is in it?

Currently I'm building a long WHERE close with OR conditions and do something like the below:

st_contains(st_GeomFromText('POLYGON(("+polygon+"))'), st_GeomFromText(CONCAT('POINT()')))


texts: Migrate database from one server to another without downtime

texts:
I want to migrate a MySQL database to another MySQL database (different server and only terminal access). I believe I can do a dump on it, but then I won't have the latest data. How do I make sure that I have the latest data if I am not allowed to have downtime? Is it possible?

Please excuse my ignorance if any (newbie in dba). I have read all the suggestions and duplicate questions before creating this new one. But none of them seems to be the one I am looking for.


texts:
I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not think it will grow to use all 1.2Tb in the next 3-5 years.

At the current rate of growth, we are not going to use up 1.2Tb in more than 14 years. More to the point, we keep migrating functionality to PostgreSql whenever we need to make serious changes. In a very dynamic agile environment, this means we frequently migrate data away out of this SQL Server database. So, this database is likely to grow even slower in the future: it is being phased out, eventually.

As such, we'd rather use this unused empty space for other databases and such - moist likely it is not going to be needed for growing tables/indexes ever.

I recall that the rule of thumb used to be "never shrink databases". Does it apply in this case? Currently the server is running 2008 R2 EE, but we plan to upgrade to 2012 very soon. I think because this shrink functionality exists, there should be valid use cases where it makes sense to shrink. Otherwise why does it exist at all?

We can afford a few hours of downtime during a weekend.

Edit: the problem we are solving is as I said above: "we'd rather use this unused empty space for other databases". Also we'd rather prefer a solution that does not require investing too much time in learning the technology we are migrating out of.


texts: MySQL - Get maximum Tuple without Grouping everything

texts:
I need some help by writing a SQL statement.

I have a table (it's already a result from another query) with 4 columns e.g.:

Firstname | Lastname | Softwarename | Hours

With a example data set like:

Row1: Max | Huber | Word | 25h

Row2: Max | Huber | Excel | 12h

The result of the query should be the first row.
My problem is that I am forced to add the Softwarename to the group by, but when I do that the result is obviously incorrect.

How do I do that?

Thank you!


texts: A Script to insert dummy Data in all tables of DB

texts:
There is an SP posted in the below link stating that it will insert dummy data in the entire DB. But the SP works only for a single table. Can anyone please share the one where we can insert dummy data throughout the db?

A Script to insert dummy Data in all tables of Database


texts:
I'm searching for a way to create a query to do the following:

Let's consider 3 tables:

products: List of products
tags: list of tags
tag_ties: table used to associate a tag to a product

Let's consider this structure for each table:

Products:

id (int, autoincrement)
name (varchar, name of the product)

Tags:

id (int autoincrement)
label (varchar, label of the tag)

Tag_ties:

id (int, autoincrement)
tag_id (int, reference to a tag id)
ref_id (int, reference to a product id)

What I want:

Obtain all the products who are tagged with tags id 10, 11 and 12 for example.

This query does not work, as it returns the products having at least one of the tags:

select
    p.name as name,
    p.id as id
from
    products p inner join tag_ties ties
on
    p.id=ties.ref_id
where
    ties.ref_id=p.id and
    ties.tag_id in (10,11,12)
group by
    p.id
order by
    p.name asc


texts: MongoDB deployment across cloud and On Premises

texts:
We are planning to implement MongoDB as a stretch cluster which includes Primary secondary in DC 1, Secondary secondary in DC 2, Arbiter in cloud(DC 3) where DC 1 and DC 2 are on premise servers and DC 3 in cloud

My questions are :

1) Should we need to keep same OS in AWS as our On-premise server or we can keep Amazon Linux also ?
In our case it is RHEL 7.2 in On premise servers

2) If No, will there be a performance impact if we use different OS between replica nodes ?

3) If yes, Do we need to do patch upgrades as frequent as we do in On Prem servers ?

4) How the failover works here in case of write concern as Majority ?

5) What are the other trade-offs in this approach if any ?


texts:
I want to make an event (concert, theater, cinema,..) booking website

venues table:

-ID
-name
-address

stages table:

-ID
-venue_id
-title
-seats

-seats is json and contain seat row and column

First question is which one is better?
Create another table for the seats of each Stage or a column for any field of seats properties?

Then I need to have a showtimes table to set the price and event_id for each seat.

I want to know, if there is an optimal way to create the showtime table instead of creating a row for each seat?


texts: MySQL replication: how to safely propagate ignored table structure changes from master to slave?

texts:
We have a MySQL database with a replication set up (master to single slave).
Some tables are skipped from replication (they are listed in Replicate_Wild_Ignore_Table when calling SHOW SLAVE STATUS). In the slave database the ignored table is present with no data.

The problem is the structure of one of the ignored tables was ALTER'ed on master and this change needs to be propagated to slave.

What is simplest non-breaking way of achieving that?
Can I just run ALTER TABLE on slave, adding the necessary columns?


texts: High paging and memory consumption in SQL Server

texts:
This is my first question about SQL Server, I'm new to SQL Server and I don't understand few things, so hope you can help me.

We got a SQL Server 2008 with 2 instances and also with Analysys Services installed (on same box).

The server is:

  2 Quad core Xeon

  80GB RAM

The SQL Server instances are configured by default, but we got times where memory consumption is high and also high pagination is presented, does this means that the memory is incorrectly configured?

Do I need to make changes in the configuration?

Actually, I'm starting to administer SQL Server, so I don't understand many things, any help or document is welcome.

Thanks.


texts: Postgresql Write Only Primary Server Bottleneck for Streaming Replication

texts:
I'm wondering about whether CPU/RAM plays an important role for the primary server for a Postgresql Streaming Replication setup where you have one primary server that receives all the write operations and hot standby servers which are used for read operations.

Intuitively the disk io is probably the most important factor but what about other factors like:

Number of CPUs
CPU power
RAM

Could you get away with having a fast SSD and skimping on the CPU/RAM? Or will something else bottleneck the write operations for the primary server?


texts: Set the file name in "SELECT ... INTO DUMPFILE"

texts:
I am trying to write a query to dump a single LONGBLOB data into a file.

SELECT data FROM table WHERE id = 123
INTO DUMPFILE fileName

which data, fileName, and id are the fields in the table.

SQL syntax error near fileName occurred when I try to run this query in HeidiSQL 10.2.0.

Does anyone know how to fix this error?

The database is MariaDB 5.5.

The following query works fine by the way:

SELECT data FROM table WHERE id = 123
INTO DUMPFILE 'data.pdf'


texts: Can Availability Groups replace transactional replication?

texts:
I've been looking at the new features of SQL Server 2012, and how it can help my current situation.

Currently we're on SQL Server 2008 R2, and replicate two OLTP servers' databases to a single reporting server via transactional replication. We then use these replicated DBs for reporting.

I'm interested in whether SQL Server 2012 and the Availability Groups can replace this - so instead of transactional replication, I'd use the new availability groups and mirror the two OLTP servers' databases to the reporting server - where the reporting will be able to access the read-only replicas.

I'm unsure if this will work well, or even at all.

I would like to get away from replication, as it causes issues with my deployment strategy (Using VSDBCMD.exe). I'd also like to get away from replication reinitialisation delays on large DBs.

Does anyone have any good examples or experience with this? Is it possible to mirror with readable replicas from multiple servers to a single server as per normal mirroring in lower versions?

This was originally asked here: https://stackoverflow.com/questions/10415225/mirroring-and-availability-groups-in-sql2012 Sorry I don't think I can migrate questions yet.


texts:
I am trying to perform a cleanup operation on a table using DELETE and I receive the following error:

  Could not allocate space for object 'dbo.SORT temporary run storage:
  140767697436672' in database 'tempdb' because the 'PRIMARY' filegroup
  is full. Create disk space by deleting unneeded files, dropping
  objects in the filegroup, adding additional files to the filegroup, or
  setting autogrowth on for existing files in the filegroup.

Before running the DELETE I have more than 11G of free disk space. When the error is issued, I have next to nothing on that partition. Context information comes below:

1) Problematic query:

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
delete from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)

2) Cardinality for involved tables

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
select count(1) from art.Article    -- 137181
select count(1) from art.Article where PublishDate < @deleteDate    -- 111450
select count(1) from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)      -- 12153045
exec sp_spaceused 'art.ArticleConcept'
-- name             rows       reserved     data        index_size   unused
-- ArticleConcept   14624589   1702000 KB   616488 KB   1084272 KB   1240 KB

3) Indexes

-- index_name   index_description   index_keys
-- IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq   nonclustered located on PRIMARY ArticleId

CREATE NONCLUSTERED INDEX [IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq] ON [art].[ArticleConcept]
(
[ArticleId] ASC
)
INCLUDE (   [LexemId],
[Freq]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

4) Server

Select @@version
-- Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
-- Feb 20 2014 20:04:26
-- Copyright (c) Microsoft Corporation
-- Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

5) Execution plan (estimated)

I understand that I am performing a large DELETE, but I cannot understand why it requires so much space to do it: the whole ArticleConcept table has less than 2GB (reserved space), yet to remove records from it requires more than 11GB.

Question: Why does my DELETE command require huge amount of temporary run storage?

I have removed all secondary indexes and I could perform the DELETE. However, why so much more space is needed to perform the DELETE when having them, looks strange to me.

I am trying to delete 12,153,045 out of 14,624,589 records (quite many). I have not monitored the transaction log, but once I received an error related to it:

  The transaction log for database ... is full due to 'ACTIVE_TRANSACTION'


texts: Mysql Cluster Restore A Backup To A Database With A Different Configuration

texts:
I have an mysql cluster which have 4 data nodes and number of replicas is 2. So I have 2 node groups with 2 data nodes in a every node group.

I planing to add 4 data nodes more to this cluster. After adding nodes i can reorganize partitions but I have to do this for every table.

Is it possible to restore a backup which was created from 4 data nodes to cluster that have 8 data nodes?


texts: Named pipes connection closed when subjected to load

texts:
We're hosting a service where we have a  messaging service. When a message arrives, it checks which subscribers it should be sent to by connecting to the database, and querying the subscriptions table.

A few months ago we started seeing the following error when the message service tries to connect:

  An error has occurred while establishing a connection to the server.
  (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)
  An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 1326)

We were at that point running on virtual servers hosting SQL Server 2008, and we were moving to a clustered SQL Server solution on physical hardware.

After the move to the physical hardware the error dissapeared, so we put it down to having something to do with the virtual servesr. However, now it is back.

It seems that the error occurs when there is high load.

Has anyone experienced why this happens? Does anyone have any experience of solving it?


texts:
I have column 'hdd' having values '1GB','500GB,''1TB' and '100TB'

When retrieving rows where value between 1TB  and 100 TB like this:

select * from product where hdd between '1TB' and '100TB'

It returns the rows that contain 1GB also.

Also tried like this:

select *
from product
where cast(replace(hdd, 'TB','') as int) between 1 and 100

It said error near ')int

I'm using mysqli.


texts:
I have table, with around 125 million records in MySql. I want to move it to mongodb.

What would be the best approach to migrate this huge table to mongodb?

I was thinking to fetch one by one row using a nodejs application and create an array for bulk insert in mongodb. But I think it will take too much memory (Haven't check it though).


texts: Function to convert first names to a specific format

texts:
I'm trying to figure out a way to show a name properly.
Let's say I have 2 variables:

@Firstname = 'Marc-Oliver'
@Lastname = 'Saint-Onge'

I would like to have name shown as

M.-O. St.Onge
Or at least M.-O. Saint-Onge

I know it's probably weird for you guys to understand this, as there are not a lot of similar situations in English, but in French this is much more familiar.
How could this be possible?
Update
In fact, there are many other possibilities for first name such as 'Pier-Luc', 'Marc-André', 'Marc-Antoine', etc. This is really familiar here in Quebec, in the French language. Forget about last name, I was thinking about it and I figured that it is much more specific so I won't touch it at all. I'll keep it as Saint-Onge and not St. Onge.


texts: Understanding of Oracle multi-tenant PDBs

texts:
I'm a new user to Oracle. The document states

  The CDB root, also called simply the root, is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.

and

  A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.

So both CDB and PDB are a collection of schema, schema objects and nonschema objects. How could PDBs belong to CDBs? What does it mean by something like schema object a belong to schema object b?


texts:
I have started working with RMAN and I have seen several comments that FORMAT should never be used when saving files to the FRA. Is there a reason for that?
For example, I use some powershell to build folders with a date and DBID in their name, before adding RMAN backups to them that night. Can I still do that in the FRA or should it be left without any FORMAT rules or sub folders? If my RMAN backup script is using CROSSCHECK, DELETE INPUT / OBSOLETE / EXPIRED files explicitly, what is the advantage of using the FRA?
Thanks in advance!


texts: Adding users to a list and sort by category?

texts:
I'm using SQL-Server 2012 and I would like to know how to solve this program.

So:

I want to create a page that allows admins to manage a project, and part of it, would be to add members/employees to the project (Project Table).

Now, I have another table, users, containing all the users on the system.

Each project has a different department (departments can be added - for example, hardware, software, accounting etc).

What I would like to solve is.. how would I do so as to create a new department and add employees to the project i.e. how will have the tables be structured, and what tables are to be created?

Steps

Add user to a project
Assign user role
Assign user department
Create department (will this have to include creation of another table?)
Add users to that department (will this have to include creation of another table?)

How can can I solve this in terms of database design?


texts: What is the advantage of using numeric primary keys in a relational database?

texts:
Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to):
create table widgets (
    widget_id number primary key,
    widget_name varchar2(20) not null,
    widget_colour varchar2(10)
);
create unique index ix01_widgets
    on table widgets (widget_name);

So there are two indices: one for the primary key, widget_id, and one for the widget_name.
Can anyone explain the advantage of having a numeric primary key over just using widget_name as the primary key? The latter has the advantage of making the contents of child tables more readable, e.g.:
create table widget_characteristics (
    widget_name varchar2(20) not null,
    characteristic_name varchar2(20) not null,
    characteristic_value varchar2(100),
    constraint pk_widget_characteristics
        primary key (widget_name, characteristic_name),
    constraint fk01_widget_characteristics
        foreign key (widget_name)
        references widgets (widget_name)
);

Otherwise (if using a generated numeric primary key) I would need to create views to see the denormalised data.
Is it something about the "indexability" of numbers versus strings? I can understand the issue of index size if the identifier (i.e. widget_name) is long, but if it's short then is that still an issue? For tables with multi-column primary keys I can understand the logic to having a generated numeric PK, but for "simple" tables I'm not clear.
Given the ubiquity of numeric primary keys I assume there is a fundamental reason for this: I'm just not sure what it is!


texts: Copy Data To Different Server using SELECT * INTO?

texts:
I'm not a DBA by any means.  I'm just knowledgeable enough with the MySQL command line to be capable half the time and dangerous the rest.

Today I was working on exporting data from a one DB into another DB.  The exercise was to take production data back into staging.  Since the table name exists in both places, a new table needed to be created in staging with a name like "_prod_copy"

First I did an export on the production server and scp'd the file over to the staging server.  On staging, I created the table in staging using:

CREATE TABLE table_name_prod_copy AS (SELECT * FROM table_name);
TRUNCATE table_name_prod_copy;

Then I used the command line to import.

Everything worked great.

I did research afterward and found this command in an example:

SELECT * INTO newtable [IN externaldb] FROM table1;

I'm going to RTFM, but also wanted to ask here:

Is there a way to tell mysql that the other DB, 'externaldb' in this case is on another server?  I know there are command line ways to export from one DB server into another DB server using SSH.  Just wondering if this command could do something similarly internal to the tool.

Thanks,

Michael J


texts: Default value for "microtime" column in MySQL

texts:
My interest is to store the time with FSP of 6. As I've read, this cannot be achieved with TIMESTAMP or DATETIME data types. So, I have a double field to store the output of the microtime function.

Is there anyway I can set (or even write some code to create) a default value for such a field?

I want to use something like NOW(6) and get 1442059062.065123 for example.


texts: Is it possible to use TSQL differential backups on a system that uses VSS?

texts:
Our colocation provider uses VSS to backup our dedicated server.  The backups show up in the SQL logs as SQL backups even though they are not and that breaks the Full-Diff chain.  The issue is well documented on StackExchange Database Administrators, but they all stop there.

Is it possible to do TSQL differential or incremental backups on a system that also uses VSS?

If not, is there a better approach for off-site backups assuming that we don't want to lose the onsite full-system VSS backups?

SQL Server 2012 SP3 if that matters.


texts: How to keep a live backup of an SQL server in sync?

texts:
For our clients we want to provide a solution of having an off-site mirror of their database.

At this moment we do this by scheduling a database-backup every night, move and import that backup in our datacentre and during the day catch each and every sql query that updates or changes data or the schema in our clients' database, send that to our datacentre and run it against the backup we imported the previous night.

This process is brittle to say at least.

What I'm looking for is a way that keeps a database in our datacentre exactly the same as our clients database realtime. Mind you, this is one-way traffic only, from the client to our datacentre.

I have been testing with replication, but that doesn't work as straightforward as I would expect it to.

Are there other straightforward ways to do this?


texts: Cannot update parent rows with trigger on the same table

texts:
I currently have the following two tables in my database

CREATE TABLE folders (
  id NUMBER NOT NULL,
  name VARCHAR2(100) NOT NULL,
  bytes NUMBER DEFAULT 0 NOT NULL,
  parentId NUMBER,
  CONSTRAINT pk_folders PRIMARY KEY (id),
  CONSTRAINT fk_folders_parent FOREIGN KEY(parentId) REFERENCES folders(id)
);

CREATE TABLE files (
  id NUMBER NOT NULL,
  name VARCHAR2(100) NOT NULL,
  bytes NUMBER DEFAULT 0 NOT NULL,
  folderId NUMBER NOT NULL,
  CONSTRAINT fk_folders FOREIGN KEY (folderId) REFERENCES folders(id)
);

There are also row triggers on the files table to update the bytes column of the folder when a row in files is inserted, deleted, or updated. This is in order to keep track of the total size of the folder. The triggers work fine in this case and successfully update the folder with the total bytes of all files contained in that folder.

CREATE OR REPLACE TRIGGER ari_files
AFTER INSERT ON files
FOR EACH ROW
BEGIN
  UPDATE folders f
  SET f.bytes = f.bytes + :NEW.bytes
  WHERE f.id = :NEW.folderid;
END ari_files;

I would like to also have the folders update their parent as well so that each folder would keep the sum of its files + any files contained in subfolders. The problem I am running into is when I try to add a trigger on the folders table to update the bytes value for its parentId. I'm getting a mutating table error (ORA-04091) because the triggers on the folders table are also updating the folders table (although it is always going to be a different row and there won't be any loops in the hierarchy)

CREATE OR REPLACE TRIGGER aru_bytes_folders
AFTER UPDATE OF bytes ON folders
FOR EACH ROW
WHEN (OLD.parentId IS NOT NULL)
BEGIN
  UPDATE folders f
  SET f.bytes = f.bytes + (:NEW.bytes - :OLD.bytes)
  WHERE f.id = :OLD.parentId;
END aru_bytes_folders;

Is there something that can be done to get this to work?

The closest that I've gotten so far is creating a temporary table and having row triggers populate the table and then having a statement trigger to read from the temporary table and process the update on its parent (which would trigger more updates). The problem that I ran into with that was that the temporary table was containing previous updates that had been processed already and the database was getting stuck in a loop.


texts: DBCC CHECKDB WITH PHYSICAL_ONLY option runs slower compared to a Full DBCC CHECKDB?

texts: Is it possible for DBCC CHECKDB WITH PHYSICAL_ONLY to run slower compared to a full DBCC CHECKDB? The platform is SQL Server 2014 SP2.


texts:
I have a table just like that:

date      sales    rest
1/1/2017   52        0
2/1/2017   30        0
3/1/2017   10        200.

I only have the rest in the last date and i want to make it like this:

1/1/2017   52        240
2/1/2017   30        210
3/1/2017   10        200.

What I want to achieve to to calculate in a new column the rest for every date from the sum of the day's sales and the last days rest.

Can someone suggest a query ?


texts: mysql high cpu usage (no have slow queries)

texts:
I have a problem with mysql. MYSQL High CPU Usage (300%-400%). But I cannot find solution to handle it. I optimized config mysql.
I think maybe my server has a problem, but What do I need to do to find the problem and how to fix it.
Infomation:

Server not have slow queries
SHOW FULL PROCESSLIST; => Only 7 connection (not lock).
show engine innodb status => ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
5 read views open inside InnoDB
Process ID=25898, Main thread ID=140213805094656, state: sleeping
Number of rows inserted 16646, updated 89843, deleted 48, read 45381116563
0.50 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2140759.81 reads/s

mysqld.log have so many line as (it happens continuously once every second):

Access denied for user 'test'@'139.198.168.221' (using password: YES)
Access denied for user 'root'@'139.198.168.221' (using password: YES)
Access denied for user 'ODBC'@'218.59.174.83' (using password: YES)
Access denied for user 'mysqld'@'218.59.174.83' (using password: NO)
Access denied for user 'root'@'180.215.129.37' (using password: NO)
Access denied for user 'server'@'112.121.189.218' (using password: NO)
[Warning] Host name 'zg-0416b-112.stretchoid.com' could not be resolved: Name or service not known
[Warning] Host name 'zg-0416a-245.stretchoid.com' could not be resolved: Name or service not known


texts: Complex Query on Large Table - Performance + Chunks [SQL Server]

texts:
I have a 9GB table with 25M rows and I need to run complex select queries to clean and add variables. This is a part of an ETL process prior to some data analysis. Queries are on my laptop running SQL Server Developer version. Data split between laptop and external USB hard-drive. I am only 'user'.

Problem: Queries are very slow (hours) and sometimes fail.

One solution: Data can be divided nicely into smaller similar-sized Chunks based on values in a single column. I can write a loop to take each Chunk in turn, perform complex query and append to output table. This seems to work - queries complete, still take many minutes but that's ok.

Questions / Issues:

I used to do this - take each chunk - using Dynamic SQL but this can be difficult to debug so now plan to do same thing with Stored Procedure (see below). Again, it works but feels somewhat messy and I wondered whether using Table Partitions could achieve same performance gains.
Sometimes you don't know what you don't know - maybe (hopefully!) I am unaware of some ways of improving this process.

Here's the stored procedure that does work on each Chunk:

IF OBJECT_ID('sqlSubset') IS NOT NULL DROP PROCEDURE sqlSubset
GO
CREATE PROCEDURE sqlSubset (@i SMALLINT) AS BEGIN
    -- get subset of large table by filtering on natural partition
    -- NOTE: use of ISNULL means stored procedure can run with NULL value for @i
    SELECT * INTO [temp] FROM [large] WHERE [x] = ISNULL(@i, [x]);
    -- do more stuff to [temp] subset here that takes a long time with original large table
END
GO

Here's the code that calls the stored procedure for each Chunk:

DECLARE @j SMALLINT;
SET @j = 1;

WHILE @j <= 5 BEGIN
    -- call stored procedure
    EXEC sqlSubset @j;
    -- if output table not yet created then create using first temp subset
    IF OBJECT_ID('dbo.large-processed', 'U') IS NULL
        SELECT * INTO [large-processed] FROM [temp]
    -- otherwise append each temp subset to output table
    ELSE
        INSERT INTO [large-processed] SELECT * FROM [temp]
    -- move to next value of j
    SET @j = @j + 1;
    -- clean up by deleting temp
    IF OBJECT_ID('dbo.temp', 'U') IS NOT NULL
        DROP TABLE [temp];
END

Any thoughts? More or less self-taught with this stuff so please be kind!


texts:
I have an application called Moodle version 1.9 and was migrated from Windows platform and Microsoft SQL Server to Linux and MySQL.

Now, when I click save changes the SQL query statement from Custom SQL, it gives me "Query failed".

I would appreciate your help to advise how to fix the below SQL statement that I got from http://docs.moodle.org/22/en/ad-hoc_contributed_reports#All_Ungraded_Assignments_w.2F_Link

SELECT
    u.firstname AS "First",
    u.lastname AS "Last",
    c.fullname AS "Course",
    a.name AS "Assignment",
    '' + a.name + ''
        AS "Assignmentlink"
FROM prefix_assignment_submissions AS asb
    JOIN prefix_assignment AS a ON a.id = asb.assignment
    JOIN prefix_user AS u ON u.id = asb.userid
    JOIN prefix_course AS c ON c.id = a.course
    JOIN prefix_course_modules AS cm ON c.id = cm.course
WHERE asb.grade < 0
    AND cm.instance = a.id
    AND cm.module = 1
ORDER BY c.fullname, a.name, u.lastname


texts: I need to set up two servers with failover capability (Active - Passive model) sharing a common DB (Postgres)running over NAS. Could you please suggest me some workaround for the same ?


texts:
What is the easier way to rename the Data Sets in completed SSRS report?
I have created a report in BIDS 2008 and now for some reasons I have to rename the Data Set names, but it seems that renaming the Data Sets in the Report Data pane, does not update the references wherever the old DataSet name has been used.

For example I have DataSet1 and want rename it to Abc. Do I have to go through all text boxes and expressions that have used the old name and rename them to the new Data set name manually? Or there is a short cut to keep them in sync.

Any idea?

Thanks.


texts: Microsoft SQL Server creating an Inline function

texts:
I am completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart datetime, and ClassStartDateEnd datetime. Where the output should be the student's GPA for all classes that were taken between ClassStartDateStart and ClassStartDateEnd. Also, supply the script to call this new function, passing it parameter values of your choice. I tried creating a code but don't know where to start. Here is what I have so far:
USE [Master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION GPAofStudents
  (StudentID int,
   ClassStartDateStart datetime,
   ClassStartDateEnd datetime)
  RETURNS decimal(3,2)
  AS
BEGIN
DECLARE AvgGPA decimal(5,2);
SET AvgGPA = (SELECT AVG(Class_GPA)
FROM Students_Classes
  WHERE Student_ID = StudentID
    AND Start_Date > ClassStartDateStart
    AND Start_Date <= ClassStartDateEnd
    AND Class_GPA > 0;
    RETURN AvgGPA;
END;

My table for the students is this:
CREATE TABLE [dbo].[Students_Classes](
    [Student_Class_ID] [int] IDENTITY(1,1) NOT NULL,
    [Student_ID] [int] NOT NULL,
    [Class_ID] [int] NOT NULL,
    [Start_Date] [date] NOT NULL,
    [Assignment1] [int] NULL,
    [Assignment2] [int] NULL,
    [Assignment3] [int] NULL,
    [Assignment4] [int] NULL,
    [Class_GPA] [int] NULL,
     CONSTRAINT [Student_Class_ID] PRIMARY KEY CLUSTERED
(
USE [A_University_Database]
INSERT INTO Students_Classes
([Student_ID],[Class_ID],[Start_Date],[Assignment1],[Assignment2],[Assignment3],
 [Assignment4],[Class_GPA])
    VALUES
('1', '5', '2010-05-30', '86', '92', '69', '99', NULL),
('1', '6', '2010-05-30', '86', '92', '69', '99', NULL),
('2', '2', '2010-05-30', '99', '85', '91', '79', NULL),
('2', '7', '2010-05-30', '99', '85', '91', '79', NULL),
('3', '3', '2010-10-01', '67', '91', '71', '100', NULL),
('3', '4', '2010-10-01', '67', '91', '71', '100', NULL),
('4', '5', '2009-02-21', '56', '93', '72', '86', NULL),
('4', '6', '2009-02-21', '56', '93', '72', '86', NULL)

What I have to do is create an aggregate inline function that displays the students class GPA. Do I need to create a stored procedure first that way the inputs are recognized?


texts: Mysql 5.7 innodb_buffer_pool_size - zero I/O?

texts:
I have a question regarding Mysql server 5.7, put the correct value for innodb_buffer_pool_size or increase RAM. Maybe I'm wrong, I'm not an expert, but let me explain what is it about.
Server configuration:
Mysql 5.7.32 server is running on Ubuntu 20.04 server, specs are 125GB of RAM, 64 cores of CPU and it have also 3TB partition for the database. SSD is nvme and CPU is AMD EPYC 7502P, so the machine is dedicated.
Here is an mysqld.cnf file conf:
[mysqld]
#
# * Basic Settings
#
#innodb_monitor_enable      =   all
#performance_schema     =   ON
default-storage-engine      =   innodb
tmpdir              =   /data/mysql_tmp
skip-log-bin
innodb_support_xa       =   0

#
# * innodb settings
#
innodb_read_io_threads      =   64
innodb_write_io_threads     =   64
innodb_buffer_pool_instances    =   64
innodb_buffer_pool_size     =   85G
innodb_buffer_pool_chunk_size   =   256M
innodb_io_capacity      =   2500
innodb_io_capacity_max      =   5000
innodb_log_file_size        =   13G
innodb_log_buffer_size      =   32M
innodb_flush_log_at_trx_commit  =   0

query_cache_size        =   0
query_cache_type        =   0
sort_buffer_size        =   10M
join_buffer_size        =   1M
read_buffer_size        =   1M
key_buffer_size         =   16M
thread_cache_size       =   100
read_rnd_buffer_size        =   1M

max_allowed_packet      =   1073741824
net_buffer_length       =   1048576
#innodb_fill_factor     =   50
max_connections         =   150
table_open_cache        =   3000
table_open_cache_instances  =   55
back_log            =   65535
wait_timeout            =   31536000
connect_timeout         =   31536000
interactive_timeout     =   31536000
net_read_timeout        =   10000
net_write_timeout       =   10000

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            =   0.0.0.0

# Procedures tunning
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
event_scheduler         =   on

The next thing that I can share is that on this server we have 18 different databases, a lot of them are small.
The main problem is with 2 big databases. First database have around 700GB and around 100 tables. Second have 200GB of data and around 15 tables. And those two constantly expands (each day).
Let me now explain what I need. I got some ticket on my work to do make adjustments with the server regarding and adjusting innodb_buffer_pool_size , and I need to calculate how much more RAM we need to add to this server so Mysql can put data in innodb_buffer_pool_size.
Because there is another service that need to pull the data in some their weird specific way and we wanna to increase speed up for them somehow.
But I'm not sure how can I do that and is it possible.
Question is:
Can we just somehow put specific large tables from these two databases in innodb_buffer_pool_size, like to tell Mysql Server just keep them in RAM bassed? Until we have some zero I/O ? Or Mysql can do there something or some other service can do this?
Some lead guy at my work told me that this is possible, like to subtract unneeded tables, or exclude tables? Combined with this "zero I/O" stuff !? Really don't know what to google and what exactly can I do there.
I am aware of memcached, also to have the other SQL server installed, but that isn't an option right now.
I've tried to tune the server with a PERL script called mysqltuner.pl , also tried with some queries similar from this page: How large should be mysql innodb_buffer_pool_size? , and I got the info like install 2TB of ram and simillar. Which for me is insane.
Hope someone can help and suggest/guide for some solution to this or maybe I'm looking wrong on this with innodb_buffer_pool_size variable.
Thank you!


texts: command line tool on linux to convert oracle sql into db2 sql

texts:
IBM has a GUI based tool to convert Oracle SQL into DB2 SQL.

Is there a command line tool (preferably on linux) that does the same thing? The reason being, we want to do this work as part of a nightly build when no humans would be around.


texts:
This morning i had a weird problem just opening the SQL developer i cannot connect to my database. I checked the log file of the impdp operation launched before (14G), all the data is imported without errors. But I cannot access to the database, I tried with the SQL Developer or with SQL*Plus.

I obtained the following error message:

I think the problem came with the log or archiving. I think i did a little mistake creating the oracle's instance. After having beed seen this error message i've seen that the oraarch folder is full of big sized files. I deleted them but this didn't solved the connection problem.

I think the problem is in the Ora parameters' file. Creating the instance in archivelog mode

 shutdown immediate
    startup mount
    alter database archivelog;
    alter database open;
    spool off

and archivelog folder and name files definition :

echo "log_archive_dest_1='LOCATION=$ORADATA_BASE/$SID/oraarch'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_format='${SID}_%r_%s_%t.arc'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora

Here the file creating when creating the instance:

echo "db_domain=''" > $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_name='$SID'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "instance_name='$SID'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_block_size=8192" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "aq_tm_processes=0" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_mode=CHOOSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "O7_dictionary_accessibility=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Remote_os_roles=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "timed_statistics=TRUE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Workarea_size_policy='AUTO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_management='AUTO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_tablespace='UNDO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_retention=1800" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "java_pool_size=0" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "filesystemio_options=setall" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "remote_login_passwordfile=EXCLUSIVE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "audit_file_dest='$STRUCTURE_ADMIN/$SID/audit'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "background_dump_dest='$STRUCTURE_ADMIN/$SID/bdump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "core_dump_dest='$STRUCTURE_ADMIN/$SID/cdump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "user_dump_dest='$STRUCTURE_ADMIN/$SID/udump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Remote_os_authent=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_index_caching=90" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_index_cost_adj=10" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_file_multiblock_read_count=16" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "control_files='$ORADATA_BASE/$SID/oractl01/${SID}_ctrl01.ctl','$ORADATA_BASE/$SID/oractl02/${SID}_ctrl02.ctl','$ORADATA_BASE/$SID/oradata01/${SID}_ctrl03.ctl'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_dest_1='LOCATION=$ORADATA_BASE/$SID/oraarch'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_format='${SID}_%r_%s_%t.arc'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "pga_aggregate_target=256M" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "processes=151" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "sga_target=2G" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "sessions=151" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
echo "Connect par SQLPLUS*"


texts: Correct place to put very large text field for mysql/mariadb

texts:
I have an app where a queue is being processed, Each time an entry is processed the result is stuck in a separate table. The queue table gets inserts and updates, the result table inserts only. The result table has a text column containing a complete context of how the queue entry was handled encoded in JSON, average length 28,000 characters.

The table is now about 800k rows and 37gigs in size and really slow to deal with when running aggregates. But this particular field with the JSON data is rarely used - kept there just in case there is some important data not parsed into it's own field already.

Moving that field to another table is not out of the question if that is the right way to go, but it will require multiple application changes. I've moved the data from MySQL 5.1 into MariaDB 10.0.14 and put it in TokuDB but found no benefit in speed, but it's compression brought the size down by about 10 gigs though.

I've made no other significant changes to any settings. If there is any tip on how to handle this without making structural changes that would be great.


texts: Performance of bitwise operators on byte column vs individual bit columns

texts:
I'm designing a database schema at the moment, but have a performance query.

Our table needs to have (at least) two flags, or boolean values.  We can call these IsComplete and IsExportable.  The obvious way to put these into a table is as two separate bit columns, but it occurred to me that we could put it into a byte column and use bitwise operators to query against it.

Which method would yield the best performance?  Both flags will be queried often, and so filtering on them should be as fast as possible.  Would an index on the byte column improve its performance?

One reason I like the byte column is because if another flag is required in the future, it can easily be made so.  Whereas with separate bit columns, an additional column will need to be added.

For clarification, the database will be stored in SQL Azure.  I suppose this might make some difference!


texts: How to completely restrict a postgres role to a single schema

texts:
I'd like to create a readonly role for a third party to access a handful of tables from the public schema without being able to view the rest of the tables in public.

My initial idea was to create a new schema 'readonly' and then create views in that schema:

create view readonly.table1 as select * from public.table1

Then alter the search path for the readonly user to limit it only to the 'readonly' schema. However, it looks like I can still view the public schema as that role (though I can't select anything from any tables in schema public).

Is there a way to remove all visibility into the public schema from a role? Unfortunately, moving everything to a different schema is not possible.


texts: HA SQL AlwaysOn backup using Copy Only + Logs from secondary replica

texts:
I'm hoping to elicit some guidance from your collective DBA experiences.

Here's our problem:

Full backups of our larger databases (around 3TB) are consuming too much resources such that we are suffering lengthy timeouts. It appears to be mostly I/O bound.

Full backups are done once a week, with regular 15min transaction log backups in between. We have to take full backups at some point.

Options we are considering:

Upgrade from SQL 2016 Standard to 2016 Enterprise edition and configure the Resource Governor to limit I/O caused by backups. Basically choke the backups a little to allow servicing of application requests better.
Go the HA (High Availability) route and deploy a secondary node with SQL AlwaysOn perform backups from the secondary replicas. This would relieve the primary replica of backup duties and remain available for application requests, potentially resolving our timeouts issue.

Pro's & Con's:

The cost of Enterprise edition is not trivial and while it offers numerous other benefits, it's a hard pill to swallow at this time.

For a similar or even lesser cost, we can deploy a second SQL 2016 Standard edition server and benefit from a HADR-based database deployment. This option is very appealing to us as we currently only have a cold standby for recovery.

We would likely favor the benefits from HADR over Enterprise, provided we can resolve the timeouts issue during backups.

Some Lab Findings:

For Enterprise edition, some labs tests show we can control the backup resources reasonably well, but it doesn't feel ideal. We'll need to tweak the numbers to the hardware to find the "sweet spot" for achieving the required level of restriction for backups without adversely slowing it down too much.

For AlwaysOn, we've done some lab tests and found that we cannot perform typical "Full" backups on the secondary replica, but we can make COPY_ONLY "Full" backups. This makes sense as it preserves the log chain of the primary replica.

We are able to make transaction log backups on the secondary replica which too is fine since that log chain is actually a "slave" from the primary and should not cause any harm when made on the secondary replica.

Have confirmed we are able to restore from the CopyOnlyBackup+TransactionLogs, so this could be a viable data recovery strategy.

Findings and Questions:

Has anyone had a similar issue and resolved it with either option, confirming viability of the option for us?
In the "CopyOnlyBackup+TransactionLogs from the secondary replica" scenario, any full backup on the primary replica would break the log chain and invalidate the CopyOnlyBackup of the secondary replica for subsequent transaction log backups. The remediation is to make another CopyOnlyBackup on the secondary replica, or to not make full backups on the primary.
What is the implication of not making normal full backups on the primary and only ever making COPY_ONLY full backups. Is this OK in the long term?

Any guidance would be greatly appreciated. Hopefully we not barking up the wrong tree.


texts: Is there a way to hint to query optimizer to MySQL which constraints should be done first?

texts:
This is my current query:

SELECT BusinessID as ID,
  111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from
(
    SELECT *
    FROM
      tableauxiliary
    WHERE
      MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1
    AND Prominent >15
) AS TA

    Having Distance <= 18238
    ORDER BY
  Distance
LIMIT
  0, 45

Notice that they I used subquery. The reason why it uses subquery is because I want

      MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)=1

to be done first. This reduces query time from 19 seconds to .9 seconds.

Is there a way to hint to mysql query optimizer so that I do not need to use subquery

Update:

I tried:

SELECT BusinessID as ID,
  111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from tableauxiliary
USE Index (LatLong_2,FullTextSearch)
WHERE
    MBRContains(
    GeomFromText (
        'MULTIPOINT(-6.1934985598076 106.76604791159,-6.1800034401924 106.77962208841)'
        ),
        Latlong)
    AND Prominent >15
    AND MATCH FullTextSearch AGAINST ('a*' IN BOOLEAN MODE)
    ORDER BY
  Distance

Nothing change. If I use explain FullTextSearch is the only index used.

Note: The question is indeed about speed. What happen is I am sure that the query will run faster if mysql optimizer do spatial first before the fulltextsearch. This is because sometimes we are searching for a* in fulltext search that obviously don't limit by much.

Basically I have businesses database for the whole world and I am only interested in businesses within 500 meter square or 10km square at most. So obviously the spatial part will be far more restrictive.

While I am moving to mongodb, it'll be kind of nice if things work too for mysql.


texts: Working out validity of table relationship

texts:
Question Background:
In a quest to improve my skill set with TSQL programming I'm designing a 'simple' database schema for a zoo.
In all I currently have 4 tables but am having an issue working out the relationship) between the zoo itself and its employees (the zoo keepers in this case).
Schema:

Is this a valid schema and relationship? Should there even be a relationship between these two tables Or should they be completely separate?


texts:
Please take a look at my mongodb map at http://postimg.org/image/krzvmep4d/

What synchronizes two delayed replicas, which is part of the replica set, included in sharded cluster? How can I use my delayed backup, if my replicaset "halfs" are not synchronized, because of I have shutdown one of my production servers first (using db.shutdownServer()), and shutdown another my server a few minutes later?

Or my mongo map just not perfect? In this case, please explain what I do wrong.

P.S. Re-added from here https://stackoverflow.com/questions/22715589/what-synchronizes-two-delayed-replicas-which-is-part-of-the-replica-set-includ


texts:
I was reading about InnoDB undo logs, and in the process, the articles i was reading ended up confusing me on what undo logs are, and how they are different from the history list, or rather the same.

So quoting article one by the MariaDB team, they defined history list as:

  There is also a global history list of the data. When a transaction is
  committed, its history is added to this history list. The order of the
  list is the chronological order of the commits.

Then in the same article, they went on to say this, which now brought even more confusion what the relationship is between undo logs, history list, and views of rows in the apparent undo logs:

  Understanding how the undo log works helps with understanding the
  negative effects long transactions.

The second article [2] by Percona describes the history list as:

  History list length 6 is number of unpurged transactions in undo
  space. It is increased as transactions which have done updates are
  commited and decreased as purge runs.

And lastly, the third article [3] tried to address the confusion by many of what the history list is, but in my opinion, just brought more confusion in distinguishing the undo logs from the history list, and how they both relate to views of rows that innodb transactions us to enforce consistency by MVCC support.Here is how the third article defined the history list:

  The best name for the unit (history list) is undo logs, which are “update undo logs
  for committed transactions”, to quote the source. But an “undo log” is
  a special term with specific meaning in InnoDB. An undo log is a
  single set of atomic changes a transaction makes, which may actually
  modify multiple records.

The first article [1] added this to even confuse me more:

  Understanding how the undo log works helps with understanding the
  negative effects long transactions.

  Long transactions generate several old versions of the rows in the undo log. Those rows will probably be needed for a longer time,
  because other long transactions will need them. Since those
  transactions will generate more modified rows, a sort of combinatory
  explosion can be observed. Thus, the undo log requires more space.
  Transaction may need to read very old versions of the rows in the history list, thus their performance will degrade.

Does the InnoDB undo log contain two types of data, one being views of rows for transaction to use stored as linked-lists, and the other being the history list, which keeps track of committed transaction in the buffer pool (not yet flushed to disk)? Can someone please help make a clear distinction between the undo logs, and how they relate to views of rows (created for running transaction to use) and the history list?

[1] https://mariadb.com/kb/en/library/xtradbinnodb-undo-log/
[2] https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/
[3] https://www.vividcortex.com/blog/2015/07/20/what-is-innodb-history-list-length/


texts: .bak file not visible in any directory in SSMS

texts:
I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it.

I can script the restore process, but I've seen this problem before and I'm not sure what could cause the .bak to not appear.


texts: PostgreSQL: Correct way for subset calculations

texts:
I have a huge table (around 10 million items). For simplicity, let's say it has only 2 columns: user_id and activity_id like this

user_id | activity_id
---------------------
1       | 1
1       | 2
1       | 3
2       | 1
2       | 2

I want to select all user_id with activity_id = 1, 2 NOT 3. In the case above it will be just one result: user_id = 2. I can do it using SELECT DISTINCT combined with INTERSECT and EXCEPT operators, but it seems to be extremely slow.

From what I know about databases, it can be improved with GIN and table partitioning, however I feel like it's not correct solution in the case of PostgreSQL (because subsets operators are slow by their own).


texts:
I am trying to copy a remote database onto my local machine using the following command

C:\Program Files\PostgreSQL\9.1\bin>pg_dump -h remote_server -U remote_user_name -c database_name | psql -a -h localhost -d local_database -U local_user_name

While doing so, it encounters an error saying data for the column not found.

Based on information from this thread I would guess that presence of characters like new line, ellipsis, right arrow in character varying column just before the column on which it errors out could be one of the reason.

So what could I do to circumvent this problem? One option is I rewrite that column, but there could many such data rows and I am not fond of this band aid type solution. I am a database noob so not sure which other documentations should I look into.

Thanks


texts: MySQL: creating a user that can connect from multiple hosts

texts:
I'm using MySQL and I need to create an account that can connect from either the localhost or from another server, i.e. 10.1.1.1. So I am doing:

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'bob'@'10.1.1.1' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT, UPDATE, DELETE on MyDatabse.* to 'bob'@'localhost', 'bob'@'10.1.1.1';

This works fine, but is there any more elegant way to create a user account that is linked to multiple IPs or does it need to be done this way?

My main worry is that in the future, permissions will be updated form 'bob' account and not the other.