texts: Open source and commercial tools to build dynamic queries of fact tables in star schema

texts: What user interfaces or tools (commercial and/or open source) can query a star schema model (Ralph Kimball dimensional model)?  So for example, if we have X fact tables, and Y dimension tables, and Z cross reference tables, we'd like to query a random fact table.  If you are aware of such tools, can you provide a reference of each.


texts: Database development/admin - What exactly should I be trying to learn?

texts:
(I tried posting to programmers.se but was told this might be a better place) My apologies if this isn't true

I've been a bit weary about approaching learning databases. I've dabbled into them before, and "DATA" in itself appeals to me a lot. Maintaining/searching/moving, everything about it in the abstract sense I love. (This isn't a career question, this is a learning question.)

But as RDBMS's begin to be easier to maintain, and with tools that "anyone" can use to manage data I feared for the database admin jobs. (Yet I see jobs for SQL everywhere!). I know "No-SQL" was a big deal but it kinda has its niche.

But that leaves me here... unsure of really "what" to study. What tools should I have in my tool belt?

I'm sure RDBMS's will be around in both use and maintaining legacy code. But what else? Obviously data will always be around, but is this a secure field or a dying one? And what should I be concentrating on?


texts: Is adding rows better then adding columns in case their value is not consistent?

texts:
In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be more products in the future, but the business won't exceed 30 products. It's easier for me to not create special products table but to add columns to customer table (each new product - new column).

People advised me against it, but I didn't get an explantaion WHY still can't understand why in such case that product's prices has no relation (each customer has his own special price) adding rows to Products table is better then adding columns of products to Customers table? The only benefit I could think of is that customer that has only 5 products won't have to 'carry' 20 nullable products (saves space on server)?

any help would be greatly appreciated


texts: Are these linked server queries susceptible to SQL injection?

texts:
I'm using a stored procedure to generate a create table script on the fly based on parameters passed into it. I am validating that the server exists, and then validating if the table exists in the database on said server.

So, first I check if the server exists:

Exists(SELECT 1 FROM sys.servers WHERE name = @Server)

If it passes this check I go on to build a string that points to Information_schema:

set @InformationSchemaPath = @Server + '.' + @DataBaseName + '.' +  'information_schema'

and then use that string in a dynamic SQL statement that gets put into a string:

set @DynamicSQL = ('SELECT count(*)
             FROM ' +  @InformationSchemaPath + '.Tables
             WHERE  TABLE_NAME = ''' + @TableName + '''')

The results get inserted into a temp table:

INSERT INTO @receiver Exec (@DynamicSQL )

I am unable to use SP_EXECUTESQL. I am stuck using Exec.


texts: Refactor two one-to-one and one-to-many relationships in a single table

texts:
I have following database schema (simplified).

Requirements

One account must have one sub-account
One sub-account, depending on type, might have other sub-accounts (nested hierarchy).

Problems I am having

Database doesn't guarantee consistency, as I can insert account without a sub-account.
Joining account and the main sub-account might result in multiple rows if sub-account has other sub-accounts when only account and the main sub-account data is needed.
Need a way to identify main sub-accounts in the sub-accounts table. ParentSubAccountId IS NULL works but not sure if it's the cleanest way to do it.

Notes

The main sub-account contains exactly the same fields, as other sub-accounts, hence was originally placed in the same table.
Sub-account children could be retrieved as a flat list with parent sub-account IDs, and hierarchical structure will be restored at software layer.

Options considered

This solves #1 and #2 problems, #3 stays the same, however I need to perform recursive select to retrieve all sub-accounts for an account.
Questions
Is there a better approach to improve database design than the option I've considered?


texts: Connection to PostgreSQL disconnects every hour regardless of activity

texts:
I'm monitoring my PostgreSQL clients using a periodic netstat -an | grep 5432 | grep EST to check the ESTABLISHED status of the connection every 5 minutes. I have noticed that all of the connections become TIME_WAIT after around minutes, and then a new connection is re-opened (the client port changes), forming a new ESTABLISHED connection. This behavior persists regardless of the status or activity of the postgres client.

Any thoughts on what might be causing this recurring reconnection on the clients? I've tried setting the tcp_keepalives_idle to no avail.


texts: PostgreSQL indeterminate ordering of results

texts:
When I have this query:

SELECT DISTINCT "spree_products".id FROM "spree_products"
INNER JOIN "spree_variants"
   ON "spree_variants"."product_id" = "spree_products"."id"
  AND "spree_variants"."is_master" = 't'
INNER JOIN "spree_prices"
   ON "spree_prices"."variant_id" = "spree_variants"."id"
INNER JOIN "spree_products_taxons"
   ON "spree_products_taxons"."product_id" = "spree_products"."id"
INNER JOIN "spree_taxons"
   ON "spree_taxons"."id" = "spree_products_taxons"."taxon_id"
WHERE "spree_taxons"."id" IN (13)
AND "spree_prices"."currency" = 'USD'
AND ("spree_products".deleted_at IS NULL
  or "spree_products".deleted_at >= '2013-08-30 06:23:45.881488')
AND ("spree_products".available_on <= '2013-08-30 06:23:45.881974')
AND (spree_prices.amount IS NOT NULL) LIMIT 12 OFFSET 36

I get some records returned in different offsets. For example, I see a record with ID=53 in both OFFSET 24 and OFFSET 36.

If I add an ORDER BY spree_products.id clause to this statement, it makes the order determinate and I never see the same record returned in different offsets.


texts: Receiving the following error in step failure of an agent job:

texts:
SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x00040EDA
Description:
"Warning: Null value is eliminated by an aggregate or other SET operation.".

Please help to find the root cause for this temporary failure and that what can be done to avoid this in future.


texts: failed with the following error: "Write on "....bak" failed: 27(error not found) BACKUP DATABASE is terminating abnormally

texts:
This issue has been puzzling me for some time now and I'm running out of ideas! Any help would be much appreciated! Here's the problem:

MS SQL Server 2008 running on Windows Server 2008R2 (Virtualized)
Database backups are failing for all (larger) Databases on a particular server. Error code 27 suggests "The drive cannot find the sector requested.".

There is plenty of available space on backup drive, and I have tried backing up to other drives on the server which also fail.

I am able to backup the system databases without any trouble. (presumably due to their small size)

I created a new blank database and was able to back this up.

Manual backups of the main Databases fail (using 'right click' or TSQL). They get to 30% and the fail as if they have run out of space.

This is a virtual server running on a two node cluster. I've tried failing the VM to the other node, but this doesn't help.

Tried splitting the backup into five parts, but the job starts hanging again at 30%.

I tried manually copying one of the old backups to the drive using Windows Explorer. The file copied successfully.

It feels like a problem with the drives, however it's only an issue from within SQL Server.


texts: Create column referencing multiple PK's from same table

texts:
I am trying to create an table, menu items, to use in my front end.

Menu items can have 1 or many submenu items.

So one column must be able to contain 1 or more ID's from other menu item records.

I am not really sure how to implement this?

I have the following statement:

CREATE TABLE [dbo].[MenuItems](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [text] [varchar](100),
    [expanded] [bit],
    [URL] [varchar](100),
    [icon] [varchar](100),
    [items] [varchar](100) NULL,
    [visible] [bit]
PRIMARY KEY CLUSTERED

Where items can contain 1/many id's of other menuItems records.

Can someone point me in the right direction?


texts:
I have a fairly simple delete query in a PostgreSQL database that I'm interfacing with via psycopg2.

Take the following minimal example:

def testDelete():
    db = DbInterface()
    cur = db.conn.cursor()
    cur.execute("DELETE FROM munamelist WHERE name='something'")
    print("Results = ", cur.fetchall())

Basically, the PostgreSQL documentation for DELETE states:

  On successful completion, a DELETE command returns a command tag of
  the form

  DELETE count

  The count is the number of rows deleted. Note that the number may be
  less than the number of rows that matched the condition when deletes
  were suppressed by a BEFORE DELETE trigger. If count is 0, no rows
  were deleted by the query (this is not considered an error).

However, psycopg2 raises a error when you try to fetch the results of the query:

Traceback (most recent call last):
  File "autoOrganize.py", line 370, in <module>
    parseCommandLine()
  File "autoOrganize.py", line 363, in parseCommandLine
    testDelete()
  File "autoOrganize.py", line 247, in testDelete
    print("Results = ", cur.fetchall())
psycopg2.ProgrammingError: no results to fetch

It doesn't matter if the item exists or not, you cannot fetch the results of the query. Does psycopg2 not return "command tags" for SQL?

If not, how can I retrieve the number of altered rows as would be returned in the console interface? Never mind, apparently cursor.rowcount is the number of rows modified in the last DML/DQL statement.


texts: Store extended properties and apply to another copy of the database

texts: I'm using SQL Doc to document a development copy of a database. SQL Doc uses extended properties to store user entered values. At some point, I would like to copy those extended properties to the production database. What is the best way to do this?


texts: Filter created_at and count distinct minutes

texts:
I have this query to get records from a database:

SELECT camera_id, count(*) AS snapshot_count
FROM   snapshots
WHERE  created_at >= timestamp 'yesterday'
AND    created_at <  timestamp 'today'
GROUP  BY camera_id;

I want to add some filters on created_at to get the count for matching rows only. E.g., the user will provide camera_id , date, time, days, for example:

From date: 2015/01/01
to date: 2015/12/30
Schedule: Monday-Friday
Timings: 9 AM to 5 pm UTC

How to apply all those to created_at to get valid records?

Update:

I also want a count per minute. If 1 minute contains more than 1 image it shall still be counted as one. You could say, only count the first snapshot per minute.

Shortly if it is 1 per 60 minutes then it will be 1 count for 60 minutes between 9 AM to 10AM and so as on to 5PM.


texts: Mysql unable to store emoji in utf8mb4 collation

texts:
I can't store emoji characters in mysql datatbase.

I searched a lot, but again fails. I have followed this tutorial and changed these variables from Variables tab in phpmyadmin.

| Variable_name          | Value
----------------------------
character_set_client     | utf8
character_set_connection | utf8mb4
character_set_database   | utf8mb4
character_set_filesystem | binary
character_set_results    | utf8
character_set_server     | utf8mb4
character_set_system     | utf8
collation_connection     | utf8mb4_unicode_ci
collation_database       | utf8mb4_unicode_ci
collation_server         | utf8mb4_unicode_ci

NOTE: set_client and set_result are also utf8mb4 in Variables tab in phpmyadmin.

I even added these lines in my.ini file:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

But again no result!! I just get ???? instead of emoji. Any idea?

Edit: SHOW CREATE TABLE emoji result

 CREATE TABLE `emoji` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `emj_text` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `emj_text` (`emj_text`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts:
This is a descriptive question regarding MySQL SQL flow architecture. I need a flow chart on the complete steps regarding MySQL SQL flow using the following components.
eg the sequence an UPDATE query goes through from memory to disk. And considering a crash at every stage how 'durability' is maintained.

innodb_log_buffer_size
log_buffer
Double write buffer
ib_logfile0, ib_logfile1 (redolog)
ibdata (Single tablespace or multiple tablespace,innodb_file_per_table=1)
binary log (WAL)
general query log (assuming enabled)

I explored and got few information but those are not sufficient and not explained clearly

MySQL Internals

XtraDb-InnoDB Internals

For Example:

First data will be written in buffer pool which contains the page cache of tables that are used recently.
If it is a READ request it is done in foreground like first it checks if the result is available in buffer pool , otherwise it hits the disk
If it is a WRITE request, the change will be written in buffer pool of table.ibd file and then to ib_logfiles .
After innodb_flush_log_at_trx_commit the changes are flushed to disk (table.ibd).

After that I don't know OR I am wrong at the initial 4 steps or missing something. Pardon me as I didn't got any reading material online on this.
At what stage it will be written in binary log (contains both committed and uncommitted transaction) or general log (contains everything including SELECT and unsuccessful queries). No idea.

Note1: Not interested in Optimizer, Parser, Other Storage Engines

Note2: Kindly do not mark this as too broad, A short precise answer covering the 7 components mentioned will be enough and really helpful for many in future


texts: If I move my SQLdata file from my F drive, delete the F drive, and rename the new Drive F will I have any file path problems?

texts: I have a windows server that only had 50 GB on the C drive and 4TB on the F drive. I need to exstend the C drive but the only way to do that is by deleting the F drive to reallocate space. If I move data from the F drive to a new drive, delete the F drive, and rename the new drive, will I loss the file path?


texts: Removing Databases in Recovery Pending Status after AG group was removed

texts:
I have removed a High Availability Group, disabled the option in sql configuration manager and restarted and I have also removed the Failover cluster components from windows and restarted.

Unfortunately the databases are marked as Recovery Pending on both servers.

I have attempted to drop them, but get this:

  Msg 3752, Level 16, State 1, Line 1
  The database 'GEO' is currently joined to an availability group.  Before you can drop the database, you need to remove it from the availatility group.

I also tried to recovery the db with no success and also set it into emergency mode but cannot:

  The operation cannot be performed on database "GEO" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

I even tried to brute force the server by stopping the engine and renaming the file to try to get it into suspect, but its still in recovery pending...  I cant re-enable the HADR settings as the windows cluster has been destroyed.

All systems catalogs are empty apart from sys.availability_replicas which has a reference to the other server (first time in a few years I wish I was back in the good old days when I could access system tables).

Any ideas how to get rid of these databases?


texts: Query the definition of a materialized view in Postgres

texts:
I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view:

SELECT * FROM information_schema.views WHERE table_name = 'some_view';

which gives you the following columns:

table_catalog
table_schema
table_name
view_definition
check_option
is_updatable
is_insertable_into
is_trigger_updatable
is_trigger_deletable
is_trigger_insertable_into

Is this possible for materialized views?

From my research so far, it appears that materialized views are deliberately excluded from information_schema, because

  The information_schema can only show objects that exist in the SQL standard.

(http://www.postgresql.org/message-id/3794.1412980686@sss.pgh.pa.us)

Since they appear to being entirely excluded from information_schema, I'm not sure how to go about this, but what I'd like to do is twofold:

Query whether a particular materialized view exists. (So far the only way I've found to do this is try creating a mat view with the same name and see if it blows up.)
And then query the definition of the materialized view (similar to the view_definition column on information_schema.views).


texts: Using strings as foreign keys in lookup table

texts:
Imagine next situation. I have an order entity which has type. For now type has such values as "photo", "video", "panorama", "supervideo" :)

Type has four values (it is possible that they will change a bit)
For each type there is associated information, such as budget factor (used for internal business logic)

So I made a lookup table with following columns: id, name, title, budget_factor

Therefore, orders table has type_id which is foreign key to id of types table.

Everything's good. However, when I started developing I came across next case.

Frontend fetch all order types
It fills <select> like <option value="{{ id }}">{{ title }}
It sends id to server when creating a new order

However, depending on the order type, server needs to receive additional information. If the type is photo server needs number of photos. If the type is video server needs number of minutes.

I use Laravel for backend development and I found it really inconvenient to work with type_id. In general situation I would write validation rule as:

"photos_num" => "required_if:type,photo"

However now I put it like

"photos_num" => "required_if:type,1" (where 1 is id of order type)

So this is bad. Of course I can write custom validation, like grab type from database, compare it's name to photo etc. Plus, everywhere where I need to check orders type, I need to do a join.

What I thought is: use photo, video etc as a PK. This way:

I can change number of types and their additional information like budget factor
I can directly compare the value of type in orders table. Like if ($order->type === 'video')

On the other hand I'm not a DBA expert and I'm not sure if it's a good practice. I have quite a lot of order characteristics, all of them are in lookup tables. For some lookup tables I can't use this method, as they have up to 10 values with long names (like middle_complexity_of_video_including_10_operators)

I can't use enums (as possible values can change + I have some associated information for every value).

So is this a good practice, or should I stick to id's?

I don't care about DB size for now.


texts:
We have a licensed SQL Server 2012 Standard Edition instance, that for some reason keeps getting license expiration messages in Management Studio.  Sometimes it gets this message:

  Your Microsoft SQK Server Management Studio evaluation period has expired.

  You can get a key to extend your trial by registering this copy of Microsoft     Management Studio online.  You may also purchase a key to activate the product.

Other times, it says a key has been entered:

  A valid product key has been entered for Microsoft SQL Server Management Studio.

Still other times, SSMS just closes, prompting you to save files.

This machine has had various versions of Visual Studio installed and uninstalled by a developer who once had administrative rights, and I suspect that's what's got this all messed up.  However, I've removed Visual Studio, and uninstalled SQL all the way to the ground, and re-installed, but the message doesn't go away.

Has anyone experienced this, and is it worth trying to fix?  Or should I install on a new platform and migrate off this machine?


texts: I have a SSRS report with a set of dynamic columns presenting summary data. I want to run a sub-report under the grouped columns but the only way I can do this is to put the sub-report outside of the dynamic columns. Therefore, when I run the report it makes the first column very wide and pushes the summary results in the dynamic columns off to the right. It's more of an aesthetic issue than anything but is there a way to keep the summary results directly above the sub-report?


texts: How to get aggregate data from a dynamic number of related rows in adjacent table

texts:
EDIT: Unknowing of the rule that prohibits cross-posting, I also asked this on Stackoverflow and chose an answer over there. Since there's another (fully working) answer in this thread though, I won't delete it. But for the solution I chose, check out this thread - https://stackoverflow.com/questions/52024244/how-to-get-aggregate-data-from-a-dynamic-number-of-related-rows-in-adjacent-tabl

I have a table of matches played, roughly looking like this:

player_id | match_id | result | opponent_rank
----------------------------------------------
82        | 2847     |   w    |   42
82        | 3733     |   w    |  185
82        | 4348     |   l    |   10
82        | 5237     |   w    |  732
82        | 5363     |   w    |   83
82        | 7274     |   w    |    6
51        | 2347     |   w    |   39
51        | 3746     |   w    |  394
51        | 5037     |   l    |   90
...       | ...      |  ...   |  ...

To get all the winning streaks (not just top streak by any player), I use this query:

SELECT player.tag, s.streak, match.date, s.player_id, s.match_id FROM (
    SELECT streaks.streak, streaks.player_id, streaks.match_id FROM (
        SELECT w1.player_id, max(w1.match_id) AS match_id, count(*) AS streak FROM (
            SELECT w2.player_id, w2.match_id, w2.win, w2.date, sum(w2.grp) OVER w AS grp FROM (
                SELECT m.player_id, m.match_id, m.win, m.date, (m.win = false AND LAG(m.win, 1, true) OVER w = true)::integer AS grp FROM matches_m AS m
                WHERE matches_m.opponent_position<'100'
                    WINDOW w AS (PARTITION BY m.player_id ORDER BY m.date, m.match_id)
                    ) AS w2
                    WINDOW w AS (PARTITION BY w2.player_id ORDER BY w2.date, w2.match_id)
                ) AS w1
            WHERE w1.win = true
            GROUP BY w1.player_id, w1.grp
            ORDER BY w1.player_id DESC, count(*) DESC
        ) AS streaks
    ORDER BY streaks.streak DESC
    LIMIT 100
    ) AS s
LEFT JOIN player ON player.id = s.player_id
LEFT JOIN match ON match.id = s.match_id

And the result looks like this (note that this is not a fixed table/view, as the query above can be extended by certain parameters such as nationality, date range, ranking of players, etc):

player_id | match_id | streak
-------------------------------
82        | 3733     |  2
82        | 7274     |  3
51        | 3746     |  2
...       | ...      |  ...

What I want to add now is a bunch of aggregate data to provide details about the winning streaks. For starters, I'd like to know the average rank of the opponents during each those streaks. Other data are the duration of the streak in time, first and last date, opponent name who ended the streak or if it's still ongoing, and so on. I've tried various things - CTE, some elaborate joins, unions, or adding them in as lag functions in the existing code. But I'm completely stuck how to solve this.

As is obvious from the code, my SQL skills are very basic, so please excuse any mistakes or inefficient statements. Also new to DBA so let me know if my question can be phrased better. For complete context, I'm using Postgres 9.4 on Debian, the matches_m table is a materialized view with 550k lines (query takes 2.5s right now). The data comes from http://aligulac.com/about/db/, I just mirror it to create the aforementioned view.


texts: How to apply Static Data Masking With Replication of Database

texts:
We used to have multiple environments for applications such as Production, Staging, UAT, Dev, and Sandbox. It is a regular task for the DBA to refresh the lower (staging, test, UAT etc.) environments with a backup of the production database backup. Sometimes we need to share the database backup with the external vendors as well.
How to Apply Static Data Masking With Replication of Databases , i.e for every change the replication database to to apply mask and save masked records in staging, test, UAT etc.
Is there any tools or process or methods available ?


texts: Auto-increment key and Foreign Key as composite for relational database

texts:
I do not have any experience in databases other than what I have from just playing with workbench and trying to understand how I need to setup this database for a project. I have been searching the internet and I have become increasingly confused as to how I will do what I want to do. Below is what I am trying to accomplish.

I have a need for a MySQL database that will hold patient information. Patient information will be name, id (unique), and so on as well as all of the tests and samples for each test.

Each patient can have multiple tests
Each test can have multiple samples
If I delete a patient then all test and samples are deleted
If I delete a test then all samples will be deleted
If I delete all samples of one test OR all tests of one patient then the test OR patient should not be deleted.

So far I understand a relational database is what I need. I also understand that I can use foreign keys to link the patient id's for each table as well as the test id for each table. I have also come to the conclusion that I would like data integrity so that I don't have to manually manage deleting and updating the rows. This would also not allow for orphaned rows. I believe this can be done with a composite key but this is where I start getting confused on how to set it up.

I would expect it to work like this (with each line being an insert statement):

Patient Table:

|-------------------|
|Patient ID  |Name  |
|-------------------|
|12345       |ANG   |
|54321       |JUE   |
|-------------------|

Tests Table:

|----------------------|
|Test ID  |Patient ID  |
|----------------------|
|1        |12345       |
|2        |12345       |
|3        |12345       |
|1        |54321       |
|2        |54321       |
|4        |12345       |
|----------------------|

Sample Table:

|----------------------------------|
|Sample ID  |Test ID  |Patient ID  |
|----------------------------------|
|1          |1        |12345       |
|2          |1        |12345       |
|3          |1        |12345       |
|1          |2        |12345       |
|1          |1        |54321       |
|1          |2        |54321       |
|2          |2        |54321       |
|2          |2        |12345       |
|----------------------------------|

Can this be done easily? I also know that this can be accomplished with one or two triggers but I understood that to not handle deletions and the like. Is there another way to do this that is easier to implement? Also, data being written and read from this database is handled by my LabVIEW program.


texts: Getting “Attempt to fetch logical page in database” error when running query

texts:
When I run queries from one of my tables I get an error

Msg 605, Level 21, State 3, Line 1
Attempt to fetch logical page (1:8687634) in database 7 failed. It belongs to allocation unit 72057594364821504 not to 72057594052476928.

I did some googling and I found this MSDN article which suggested running DBCC CHECKDB. I ran the check but it reported 0 allocation errors and 0 consistency errors so I'm not sure where to go from here.
From other sites I saw that the problem might be a corrupted index so I deleted the (only) index of that table and I still get the same error when running a query.
I did DBCC CHECKTABLE and got

Incorrect PFS free space information for page (1:8687634) in object ID 270624007, index ID 0, partition ID 72057594508083200, alloc unit ID 72057594052476928 (type In-row data). Expected value  50_PCT_FULL, actual value   0_PCT_FULL.

It's not in emergency mode or anything, is there a way to repair/rebuild this table without going into single user mode?
I ran dbcc page(7, 1, 8687634, 3) with a result of

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.


texts: Query performance low for aggregate function

texts:
I perform a  SUM query on a MySQL table of mine which takes around 1.5 seconds to complete. The table will have around 200000 rows at any time. I have to run this query every 5 seconds from my web app and because of that my DB CPU usage is shooting up to around 50%.

This is my query

select ((SUM(active_energy) *0.00277)/1000) as kwh
from energy_readings
WHERE  unitid = 2000

I am using an Amazon RDS instance (db.m4.large). Is there an alternate way of writing this query with low server load?

My table structure is like this

Columns:

Id bigint(20) AI PK
unitid varchar(16)
date_time timestamp
active_energy float
power_factor float

CREATE TABLE energy_readings (
 Id bigint(20) NOT NULL AUTO_INCREMENT,
 unitid varchar(16) DEFAULT NULL,
 date_time timestamp NULL DEFAULT NULL,
 active_energy float DEFAULT NULL,
 power_factor float DEFAULT NULL,
 PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=6114324 DEFAULT CHARSET=latin1

I use this code to fetch the values. This works with string unitids as well; Should I be putting quotes around it?

$stmt = $db->prepare("
    select ((SUM(active_energy) *0.00277)/1000) as kwh
    from energy_readings
    WHERE unitid=?
");
$stmt->execute([$_SESSION["plug_id"]]);


texts: How can I create 3 tables with references On SQL Server

texts:
I started using SQL Server recently and I think this is simple to resolve.

I need to create one table with a country list, and two tables that will use the country list in a column.  Here is the code:

1 - (uf to log a country list)

CREATE TABLE uf
(
id int primary key,
uf varchar(2), (dont know if can I use a column with the same name of the table)
);

2 - the resumed table that will use the list.

CREATE TABLE carta
(
id int primary key,
uf varchar(2) not null,
);

3 - the resumed table that will use the list.

CREATE TABLE cliente
(
id int primary key,
uf varchar(2) not null,
);

I'm really thankful if someone helps me

If have some errors in the writing, I'm sorry, I'm from Brazil and don't know English very well


texts: How to determine the size of my table per column type in the SQL Server database

texts:
I want to see how much mb used in table per column type. For example;

 TABLE 1
 type       size(mb)
-------    --------
 bigint     300mb
  bit        10mb
 char       ....
 datetime   ....
  varchar     ....

I used a query, i can only find type name and sum of max_length of column whole database;

Select t.name as 'type',sum(c.max_length) maxlength from sys.Columns c
inner join sys.tables tb on tb.object_id=c.object_id
inner join sys.types t on c.system_type_id = t.user_type_id
where tb.is_ms_shipped=0 group by t.name

What should i do more to find that statistics on one table then whole database? thanks


texts:
Thanks for looking at this, and thank you in advance for any answers!

I am attempting to start a mirroring session, but I am getting an Error 1418, which basically states that the TCP port doesn't exist. However, when I look at the SQL Logs, I am seeing a completely different error. It says that the login does not have CONNECT permissions on the endpoint.

Here is the problem, I do not know where this Login is coming from. It doesn't exist. It's never existed. It's not the login that I am using when connecting to either the Principal or the Mirror.

I can't create this login because there is a '$' in it, which again, makes no sense. Any help would be astronomically appreciated! Thank you.


texts:
I am trying to set up a MySQL slave to use replication via SSL.

The my.cnf file contains the following:

server-id = 1012

master-host = host
master-port = port
master-user = user
master-password = password

ssl-ca=/etc/mysqld/ssl/ca-cert.pem
ssl-cert=/etc/mysqld/ssl/client-cert.pem
ssl-key=/etc/mysqld/ssl/client-key.pem

Starting the slave fails with SLAVE STATUS showing

Master_SSL_Allowed: No

When I manually set up the slave as follows:

CHANGE MASTER TO
     MASTER_HOST='host',
     MASTER_USER='user',
     MASTER_PASSWORD='secret',
     MASTER_SSL=1;
START SLAVE;

Everything works.  Any idea what parameter I need to enter into my.cnf in order to force the slave to use SSL?  The obvious choice - "master-ssl=1" did not work.


texts: Redshift postgresql time series data index DISTKEY and SORTKEY

texts:
I have around 6 billion rows of data to move to a redshift DB from MSSQL. The data will have three columns.

I plan to move the data one tag at a time as there are 4 years of history at one-minute intervals so the data will be out of time order, as this will be easier than moving all 4000 at once for a given time period in chunks.
I have read about DISTKEY and SORTKEY although not sure how I would implement for the best performance. Would anyone have any advice, should I split the table up into multiple tables so that it is not so long and how would I use DISTKEY and SORTKEY to improve performance.
Note: Redshift postgresql is the only option I have been given at this points.
Thank You


texts:
I've read the following code a few times trying to figure out what it is doing.

  "A right outer join is that which returns the unmatched rows from the right tables with matched rows from the left table."

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? I'm preparing for 1z0-047 so any help would be appreciated.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;

I thought that the column should exist. But here is the Oracle docs page with all example schemas. If I search with ctrl+f for item_cnt, I find nothing: Oracle example schemas.


texts: Why should a CTE start with a semi-colon?

texts:
I was just looking at a post on StackOverflow where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand.  My question is, why does the first line of the CTE begin with a semi-colon?

;WITH n AS (SELECT TOP (10000) n FROM
  (SELECT n = ROW_NUMBER() OVER
    (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
  ) AS x ORDER BY n
)
SELECT n FROM n ORDER BY n; -- look ma, no gaps!

Is this to ensure the WITH statement does not get parsed into a previous SELECT or something?  I see nothing in SQL Server 2005 BOL about using a semi-colon prior to the WITH.


texts:
Which SQL Server system database is more important: master or resource?

If the resource database is damaged, can the database engine work?

Can the system work with only the master database?  If not, which system databases are necessary?


texts: Should the Query Store be enabled on all databases?

texts:
We are using SQL Server 2016 Standard Edition (SP2-CU2). We have a few servers hosting each between a few dozens to hundreds of databases. We have never enabled Query Store on our databases.
I know there are lots of advantages to turning on the Query Store for tuning. I was wondering, are there any cases when the query store should not be turned on? Or should I turn it on on all databases?

EDITED

Just in case it can help other people. What I've ended up doing was enabling the query store a few databases at the time, every day, and monitoring. So far, I've had the query store enabled on almost all of my databases without any performance impact. It has proven itself useful when investigating problems.


texts: Disable CertificateRequest in ServerHello in MySQL/MariaDB

texts:
I configured SSL (not mutual SSL) in MySQL. I created a user like this.

CREATE USER 'username'@'%' IDENTIFIED BY 'passowrd';
GRANT ALL ON db_name.* TO 'username'@'%' REQUIRE SSL;

But, in ServerHello, I get this.

*** CertificateRequest
Cert Types: RSA, DSS
Cert Authorities:
<Empty>
[read] MD5 and SHA1 hashes:  len = 9
0000: 0D 00 00 05 02 01 02 00   00                       .........
[Raw read]: length = 5
0000: 16 03 02 00 04                                     .....
[Raw read]: length = 4
0000: 0E 00 00 00                                        ....
localhost-startStop-1, READ: TLSv1.1 Handshake, length = 4
*** ServerHelloDone

This forces the client to send the client certificate. Since I don't want mutual SSL, I want to avoid this.

Basically, I want to remove CertificateRequest part from ServerHello. Is there a way to achieve this by some confguration? Appreciate your help.


texts: changing domain name for Mongodb primary

texts:
Im trying to initialize my replica set sp that the primary node has a specific domain:port pairing. what I want is:

  "vagrant-ubuntu-trusty-64:27022"

but what I get is:

  "localhost:27022"

This is the command I use to start the mongo instance:

  sudo mongod --port 27022 --dbpath /db/config/data --configsvr --replSet config

This is what I use to access the mongo instance:

  mongo --port 27022

I tried to modify my host file to add the domain name to the default domain of 127.0.0.1, but that didn't work.

127.0.0.1 localhost
127.0.0.1 vagrant-ubuntu-trusty-64
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

How do I get the specific "vagrant-ubuntu-trusty-64:27022" pairing when I initialize the mongo instance?


texts: Compile TSQL stored procedures into assembly

texts:
I am trying to protect my business logic and I don't want to use "With Encryption" as it is not enough and can be broken.

I don't want to re-write the procedures again in CLR language.

Is there an easy way to compile the TSQL programming into a DLL that can be used by SQL server, just like the same way it uses the CLR assemblies?


texts: How can I update multiple tables at once in Postgres?

texts:
I have several tables with the exact same structure and I need to update a value in all tables.

In order to do so I tried to build the following script:

DO
$do$
DECLARE
  i pg_tables%rowtype;
BEGIN
FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
LOOP
    UPDATE i.tablename SET name = replace(name, '.js', '.ts');
END LOOP;
END
$do$;

I could see that i.tablename has the correct value (I inserted in a tmp table to check), but the update fails.

name: error
length: 223
severity: ERROR
code: 42P01
internalPosition: 8
internalQuery: UPDATE i."tablename" SET name = replace(name, '.js', '.ts')
where: PL/pgSQL function inline_code_block line 7 at SQL statement
file: parse_relation.c
line: 965
routine: parserOpenTable

Just plugin i.tablename on the UPDATE statement doesn't work.

Is there a way to make it work? Or an easier way to update all tables at once?


texts: Permission Error when creating Linked Server from SQL Server to Access DB

texts:
I created a linked server from my SQL Server 2008 R2 64 bit Windows Server 2008 machine to an Access 2000 Database that resides on another server.
The Linked server was created with no trouble using SSMS on the SQL Server machine, and you can browse the tables no problem. The trouble is trying to access the linked server from my local windows machine using SSMS authenticated with my windows account.
I have sysadmin privileges, and per several suggestions seen on the web, I have added 'Full control' shared permissions to the local service and network service temp directories.
In desperation, I also copied the MDB file to the actual SQL server machine, set up a linked server and found that it works fine when querying from my local machine's SSMS.
So, in summary, SQL Server needs to link to an Access DB on another Server and I must be able to query said Access DB from my local SSMS or some other SSMS instance or application (like SSRS).
Linked Server Settings:

Provider = Microsoft.ACE.OLEDB.12.0
Product Name = Access
Data Source = \\ServerName\path\to\Datafile.mdb

Error Message (When Running Test Connection):

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Server".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Server" returned message "The Microsoft Access database engine cannot open or write to the file '\path\to\Data.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.". (Microsoft SQL Server, Error: 7303)

I don't know if there is any other information you need, but I will be happy to provide it to any who are willing to help. If you need clarification, then please ask as well.
Thanks!


texts:
thanks for reading.

I am currently working with an Oracle 11G.I have a problem with a tablespace, I have very little space left, and I need to increase its capacity.I know what to do with this command:

alter tablespace datafile '+DG_DATA/sificore/datafile/auditoria.282.999190187' resize 160000m;

But when I'm trying to find the dbf, it appears to me like this.

My question is this:

I can't find the file with the dbf extension, can I run the command with what appears to me there (+ DG_DATA / sificore / datafile / audit.282.999190187) and would it have no problem?

the database, is currently in active redolog mode, would it have conflicts?

Should I do the issue of putting it in online, offline and recover datafile mode?

Thanks for reading, I will greatly appreciate your help.


texts: How do I efficiently get "the most recent corresponding row"?

texts:
I have a query pattern that must be very common, but I don't know how to write an efficient query for it.  I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table.

I have a table, inventory say, which represents the inventory I hold on a certain day.

date       | good | quantity
------------------------------
2013-08-09 | egg  | 5
2013-08-09 | pear | 7
2013-08-02 | egg  | 1
2013-08-02 | pear | 2

and a table, "price" say, which holds the price of a good on a given day

date       | good | price
--------------------------
2013-08-07 | egg  | 120
2013-08-06 | pear | 200
2013-08-01 | egg  | 110
2013-07-30 | pear | 220

How can I efficiently get the "most recent" price for each row of the inventory table, i.e.

date       | pricing date | good | quantity | price
----------------------------------------------------
2013-08-09 | 2013-08-07   | egg  | 5        | 120
2013-08-09 | 2013-08-06   | pear | 7        | 200
2013-08-02 | 2013-08-01   | egg  | 1        | 110
2013-08-02 | 2013-07-30   | pear | 2        | 220

I know one way of doing this:

select inventory.date, max(price.date) as pricing_date, good
from inventory, price
where inventory.date >= price.date
and inventory.good = price.good
group by inventory.date, good

and then join this query again to inventory. For large tables even doing the first query (without joining again to inventory) is very slow.  However, the same problem is quickly solved if I simply use my programming language to issue one max(price.date) ... where price.date <= date_of_interest ... order by price.date desc limit 1 query for each date_of_interest from the inventory table, so I know there is no computational impediment.  I would, however, prefer to solve the whole problem with a single SQL query, because it would allow me to do further SQL processing on the result of the query.

Is there a standard way to do this efficiently? It feels like it must come up often and that there should be a way to write a fast query for it.

I'm using Postgres, but an SQL-generic answer would be appreciated.


texts: I just issued a shutdown immediate command, and it's taking a lot of time. Is there any way, I can find the status of the shutdown task in terms of percentage completed? Or, any kind of status update. It's driving me crazy not knowing what's happening. I know it rolls back uncommited transactions, but I want to know how much of that has been done, how much is left etc.


texts:
We have one project which has Failover Cluster with two nodes and one standalone server in AlwaysON. For example:

Project 1

Node1 (ip:192.168.100.101)
Node2 (ip:192.168.100.102) in Failover Cluster with clustered IP: 192.168.100.104
Node3 (ip:192.168.100.103). Clustered IP:(192.168.100.104) and it's AlwaysON IP: 192.168.100.105

And now we have another project as follows (Cluster IP:192.168.200.105):

Project 2

Node1 (ip:192.168.200.101)
Node2 (ip:192.168.200.102)

We want to add third Node3 (ip:192.168.200.103) into Project2 cluster and create AlwaysON. But don't want change Cluster IP. In Project1 we have two WFCI. Can we create with one WFCI and Clustered IP?


texts:
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.

I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between two different instances. Indexes on Primary table keep getting changed and I need the empty stage_table with all the updated indexes when I do the partition switch.


texts: Storing data for machine learning in MySQL

texts:
I am currently in the process of developing a machine learning application, where my users can upload their own data to train models. Furthermore, the users must be able to extend their uploaded data with new data (append new data to it). I am in a bit of doubt when it comes to actually store this (and whether or not it should be in a DB)
The data source can be:

CSV file
Excel file

As each file is unique, the column names will also be dynamic. For example, see below:
// File_1.csv
comment
----------
Some text here
another text here

// File_1.xlsx
ingredient
----------
tomato
strawberry

At some point, after the data has been uploaded, the user should be able to extend it (append new data to it). For example:
// File_1 data:
text added at a later date

Labels
Now, after the data has been uploaded for each model, a user must be able to add labels to it (for example for text classification). For simplicity, let's assume that we have two labels: label_one and label_two.
In order to store this, I was thinking of creating a table called model_data.

Inside this table, I would, for each model, save the data in the data column (in JSON). Something like:
{
   "rows":[
      {
         "text":"Some text here",
         "labels":[
            {
               "label_one":true,
               "label_two":false
            }
         ]
      },
      {
         "text":"Another text here",
         "labels":[
            {
               "label_one":true,
               "label_two":false
            }
         ]
      },
      {
         "text":"tomato",
         "labels":[
            {
               "label_one":false,
               "label_two":true
            }
         ]
      },
      {
         "text":"strawberry",
         "labels":[
            {
               "label_one":false,
               "label_two":true
            }
         ]
      },
      {
         "text":"text added at a later date",
         "labels":[
            {
               "label_one":false,
               "label_two":true
            }
         ]
      }
   ]
}

Is this even a feasible way to store such data? Typically, data for ML tasks often consists of large datasets with thousands of rows.
Alternative
I have also considered just using a filesystem to maintain this, and then store data for each model in a CSV file. So whenever a user uploads some data (and extends it), it will be added to a new CSV file.


texts: Does Oracle RAC require that the Ethernet aliases be in a different IP range than the base addresses?

texts:
Let's say I have 3 nodes running RAC. The base addresses (eth0 interfaces) for those nodes are in the 192.168.1.0/24 range.

From what I've seen RAC requires that you create Ethernet alias interfaces (eth0:0) on each node, with addresses in a different range. But is that a requirement? Isn't it possible to create eth0:0 with just other addresses within 192.168.1.0/24?


texts: Install MySQL for Windows from .zip and reset root password

texts:
I wanted to have on my PC (with Windows 8.1 x64) the MySQL server only, without Workbench or something. So I downloaded .zip archive from dev.mysql.com/downloads. It's a download for Win64 on x86_64 version 5.7.9 (MySQL Community Server (GPL)).

I installed it as a Windows service, but there were no mysql database, only information_schema. So I executed this:

mysql_upgrade.exe --upgrade-system-tables

And mysql databases were created. But along with it something happened with root user, because I couldn't access mysql anymore.

So I decided to reset this suddenly appeared password (because I didn't have it before that). I founded the following solution in the official manual, I started the server like this:

mysqld.exe --skip-grant-tables --console

Then I opened mysql without password:

mysql.exe –u root

And then tried to reset the root password:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD(‘passhere');

But I got this error:

  ERROR 1131 (42000): You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords".

What is this? How am I anonymous? All commands were executed in cmd.exe under the Administrator.

What should I do to reset the root password in this situation?

Update 1: I tried to check the current user:

SELECT USER(), CURRENT_USER();

That gives:

+--------+----------------+
| USER() | CURRENT_USER() |
+--------+----------------+
| root@  | @              |
+--------+----------------+

That's weird, because I started it like mysql.exe -u root.

Then I checked users table:

SELECT user FROM mysql.user;

That gives:

+-----------+
| user      |
+-----------+
| mysql.sys |
+-----------+

That's even more weird. Also there is no password field:

SELECT user, password FROM mysql.user;

  ERROR 1054 (42S22): Unknown column 'password' in 'field list'

So I cannot change its password.

And I cannot create a new user:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'passhere';

  ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Now I'm lost.

Update 2: I guess, I did everything wrong from the beginning. Apparently, I missed a mysqld.exe --initialize command somehow.

So, this is how I installed MySQL from .zip-archive, thanks to @RolandoMySQLDBA:

Unpack archive, prepare my.ini.
Execute mysqld.exe --initialize, get temporary password from the .err file.
Install MySQL as a service and run it

mysqld --install MySQL --defaults-file="d:/path/to/mysql/my.ini"

Connect to it mysql -u root -p with temporary password.
Change the temporary password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';


texts:
Is there a way to restart replication without going over MySQL :: MySQL 5.1 Reference Manual :: 16 Replication.

I'm NOT seeing ANY errors on master and I'm not entirely sure why slave is experiencing issues such as following:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: Master_Host-FQDN.TLD
                  Master_User: Master_User-REPL
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000057
          Read_Master_Log_Pos: 247673142
               Relay_Log_File: mysql:PR_DB-relay-bin.000041
                Relay_Log_Pos: 5537565
        Relay_Master_Log_File: mysql-bin.000044
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: RDD1,RDD2,RDDX
          Replicate_Ignore_DB: information_schema,mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 25338679
              Relay_Log_Space: 2190669763
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

--log-error=/var/log/mysqld.log (slave):

140630  9:10:53 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 255, event_type: 2
140630  9:10:53 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
140630  9:10:53 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
140630  9:10:53 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000044' position 25338679

Can I use CHANGE MASTER TO (MySQL :: MySQL 5.1 Reference Manual :: 16.1.1.10 Setting the Master Configuration on the Slave) and point to respective values:

MASTER_LOG_FILE = Relay_Master_Log_File,
MASTER_LOG_POS Exec_Master_Log_Pos.


texts: Do I need to reenter old data after adding an index to a table?

texts:
I want to add index to my tables. Some of my tables already have couple thousand rows.

Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing affect them as well  or it's going to take care of old data itself ?


texts: "Replicate" sql structure between testing and staging on same database instance

texts:
On my local development server I have a database set up which gets hooked into Entity-Framework and is then used for unit testing. Let's call it COMPANY\Database

On the same server (and same database instance) I then have another database - COMPANY\DatabaseStaging which has the exact same structure but persistent test data used for constructing a front end site around.

So, to hopefully clarify, the structure/flow is something like:

Create or modify a table:

  COMPANY\Database.Product

Update entity framework, write some business logic, unit tests, etc against the table.

Manually mirror/replicate the changes:

  COMPANY\DatabaseStaging.Product

Put in some slightly more persistent test data and do front end/ASP.NET stuff.

Now, this doesn't seem very elegant to be manually updating the COMPANY\DatabaseStaging database to make it reflect changes in COMPANY\Database. Is there a better way of doing it?


texts: Query to output column names and data types of a query, table or view

texts:
Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view?

E.g., a solution if applied to simple SELECT query like SELECT * from person should return a list like:

Column Name   | Column Type
===========================
First Name    | character
Last Name     | character
Age           | integer
Date of Birth | date

I have looked up the information_schema views described in an answer below and it seems to cover tables quite well, and I suspect it covers views as well but I haven't  checked that yet.

The last is any arbitrary but valid SELECT query eg involving, JOINS,  UNIONS etc, on the database. Is there a built-in procedure, or other stored procedure or script that can return the same for any valid QUERY at all?

I am developing a program that creates data and querying forms and the information is needed for data validation and executing functions on the returned data.


texts: Remove substring from one column and put it on other column

texts:
I have a table like this:

CREATE TABLE `mytable` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `column1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `column2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And some data:

id | column1                                       | column2
____________________________________________________________
1  | Something                                     |
2  | Something else - Lets add more data           |
3  | Something else - Another example - More stuff |

I would like to update this table, by putting everything after the first - (space + dash + space) into column2 and then removing it from column 1. If there is no -, leave it as it is. Such as this:

id | column1        | column2
__________________________________________________
1  | Something      |
2  | Something else | Lets add more data
3  | Something else | Another example - More stuff

Any suggestions?


texts: Size of TempDB Microsoft SQL Server 2005

texts:
Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full.

I am creating large temporary tables in a couple of queries. Once I am finished running queries, I drop the temporary tables. However, tempdb remains relatively large. Should I be worried about any subsequent problems with the server? Such as performance, or something I am not able to think of.


texts: Role or permission to view but not create SQL Agent jobs

texts:
The Microsoft SQL Server database role SQLAgentReaderRole apparently lets a member create SQL Agent jobs along with viewing jobs, job steps, and results.

We need to allow a user to see all SQL agent jobs, steps, schedules, and results, but not create any jobs.  This can almost be provided using several different views, but the results are messy and hard to read -- I want to be able to see the SQL Agent node in Management Studio and verify things.

Is there any role or permission that would allow this?  Let's assume SQL 2008 through SQL 2016.  Thanks.


texts: Why is it not possible to switch SQL mode on MySQL 8 running on Mac?

texts:
I am getting an error in MySQL 8 (local install) running the same queries as on MySQL 5.7 (production) regarding SQL mode. Trying to switch the SQL mode results in restart errors of MySQL. Version: 8.0.23 on OSX 11.2.3 runing on Intel
SQL error:
vl=eror msg="query error" logger=tsdb.mysql err="Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.a.END_TIME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

Chaning mode as described here
My.cnf
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restart failes.
MySQL erorror log:
2021-03-27T10:51:58.916408Z 0 [Note] [MY-010120] [Server] Binlog end
2021-03-27T10:52:09.208939Z 0 [ERROR] [MY-000077] [Server] /usr/local/mysql/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2021-03-27T10:52:09.209219Z 0 [ERROR] [MY-010119] [Server] Aborting

Any ideas on how to fix this?


texts: How to secure SQL Server in an enterprise environment?

texts:
So I've been tasked with learning how to secure our SQL Servers.  Here's the scenario:

Individual users have read access to a table in SQL Server.  This table have apx. 33 million rows, and growing (telemetry data).

Some genius user discovered Power BI and installed it (Desktop), along with the on-prem data gateway on their workstation.  Then they did the same thing for 40 of their friends.

THEN they created a .pbix report to query this table.
THEN they published the report to their personal workspace.
THEN they emailed the report to their 40 friends with instructions on how to set up the gateway on each workstation - and how to enable the data sync schedule for twice a day.

So now our 33 million records are exiting our network (40 users * twice a day = 80 times per day).  Besides being a strain on the SQL Server - we just don't want that  data being published to a service we don't have any control over by users that aren't authorized to be doing that.

So the questions are:
How can we prevent this scenario?  What's the proper way to set up a database so a user can access the data, but not be able to publish the data off-site?  Are there firewall rules we can set up to block the gateway service?  My understanding was it just needed port 443 outbound - which we obviously can't block.

Any recommended training courses that cover this kind of stuff?

Thanks!


texts: Oracle impdb on windows generates a subdirectory

texts:
I am importing to a windows oracle, a dump file produced on Linux. But oracle generates a subfolder in the dpdump directory /76E0F91BE49D480CB2633847BD640FC5\  and causes an "ORA-31640: unable to open dump file" error.

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "E:\oracle\admin\orcl\dpdump\/76E0F91BE49D480CB2633847BD640FC5\backup_DB_01.dmp" for read
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

I shut down oracle and deleted that long subfolder. But it gets recreated every time I run impdp. Any suggestion?


texts: pt-table-checksum and mysql 5.7 with binlog_format=ROW

texts:
How to use pt-table-checksum in mysql 5.7 where binlog_format=ROW is default

I also need binlog_format=ROW to be set this way in relation to other parameters in the mysql configuration

# pt-table-checksum h=localhost,u=checksum,p=xxx
Replica Worker-Intel-i7-2600-16gb has binlog_format ROW which could cause pt-table-checksum to break replication.  Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation.  If you understand the risks, specify --no-check-binlog-format to disable this check.

I have tried to read about the limitations, but doesn't really understand what would/could happen if --no-check-binlog-format is used

If I set binlog_format=MIXED I still get the error!?


texts: how can I update some table rows using data from existing rows?

texts:
I have created a table where it stores some multilingual store data with this structure

create table store_locator_store_lang
(
    id_store int(11) unsigned not null,
    lang_id int not null,
    partner varchar(100) null,
    address varchar(255) null,
    city varchar(100) null,
    occupation varchar(100) null,
    primary key (id_store, id_lang)
);

id_lang can be 1 or 2

In there I have some incomplete data meaning that there are all the rows for lang_id = 1 but for lang_id = 2 many rows contain empty values.

I would like to populate the empty rows for lang_id = 2 with data from the rows with lang_id = 1.

The store entity is identified by id_store so the id_store is common for the two languages of the same store.

There is always a pair stored even if one row has empty columns. There is no chance there is a row missing for lang_id=2

Is it possible to accomplish this task with an update query in the database or do I have to export the data, edit and reinsert?

The Database server is  10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Here's a dump of some example data

INSERT INTO store_locator_store_lang (id_store, lang_id, partner, address, city, occupation) VALUES (543, 1, 'TEST', 'TEST, Τ.Κ. 52100', 'TEST', 'TEST');
INSERT INTO store_locator_store_lang (id_store, lang_id, partner, address, city, occupation) VALUES (543, 2, '', '', '', '');


texts:
I'm building an application where a certain group of programmatically-created Azure SQL Server tables will only ever be read from.

The app will create a new table, insert a bunch of data, and then serve queries from that data. After the initial inserting the data will never be changed in any way.

Given this usage pattern, are there efficiencies I could take advantage of when setting up such a table, or the indexes on it?


texts: UPSERT - Is there a better alternative to MERGE or @@rowcount?

texts:
I was wondering if you have encountered a T-SQL command similar to the concept of UPSERT?  Performing INSERT|UPDATE operations using options (1) or (2) seems overly complex and error prone.

OBJECTIVE

To ensure that the desired record (in this case employee_id 1) is up-to-date WITHOUT having to having to essentially write the same query twice.

CONTEXT

table name: employee
employee id: has a primary key, and identity proerty is set to true

OPTIONS

execute a SQL UPDATE... check @@rowcount = 0 and @@error = 0... execute SQL INSERT if required

con: you effectively have to write the same query twice, once as an
insert, once as an update
con: more code = more time typing
con: more code = more room for error

https://stackoverflow.com/questions/1106717/how-to-implement-a-conditional-upsert-stored-procedure "Update using @@rowcount"

execute a SQL MERGE

con: you effectively have to write the same query twice, once as an insert, once as an update
con: more code = more time typing
con: more code = more room for error

http://technet.microsoft.com/en-us/library/bb510625.aspx "T-SQL Merge"

execute a SQL UPSERT (feature does not exist)

pro: you define the data-to-table relationship once (let SQL Server worry about whether or not it is an INSERT or an UPDATE)
pro: less code = faster implementation
pro: less code = lower probability

UPSERT EXAMPLE

UPSERT employeee (employee_id, employee_number, job_title, first_name, middle_name, surname,  modified_at)
VALUES (1, '00-124AB37', 'Manager', 'John', 'T', 'Smith', GetDate());

if employee_id 1 does not exist: MS SQL executes a INSERT statement
if employee_id 1 exists: MS SQL executes and UPDATE statement


texts: Is it better to start with a smaller INT types and grow as needed?

texts: I am designing a new SQL Database for my ASP.net web application and I can foresee that some of the ID columns will get very large in the feature need INT and BIGINT but that will not be for a few years. With Entity Framework in ASP.net it is fairly easy to change column data types. So I was wondering, from a design point and performance point, is it better to start off using the smaller column types like TINYINT and SMALLINT and grow to INT and BIGINT when the time finally comes?


texts: MySQL: How to set the default string using another field by string-interpolation?

texts:
I have the following table and I wondering whether string interpolation using another is possible:
CREATE TABLE Account (
    AccountID varchar(25),
    About varchar(300) NOT NULL DEFAULT
        'My id is ${AccountID}',
    PageLink varchar(255) NOT NULL UNIQUE,

That is: I want different default values for each row using the primary key.
id: 1
About: My id is 1

id: 2
About: My id is 2


texts:
I used Database Engine tuning advisor for  indexing a table and when I am checking indexing  , it showing multiple indexing on same table . as shown in
fig

I am wondering for these question  as follows :-

what will be the effect if i delete the blue highlighted indexing  because it and indexing below it has 2 columns common .
Any effect on the performance ??
why multiple indexing ??
4 . any other suggestion , other then delete ..

thanks in advance......


texts: where is the index physically located in MySQL database

texts:
Just wondering where is the index info physically located in MySQL database if I have indexed a table with ALTER TABLE contacts ADD KEY (columnName); ?

it will stay with that table and add a new spacial column to it;
stay separately from that table;

I think the #2 is the correct answer.

Can we index columns from more than one tables? how?


texts: Allow only parent DELETE cascade to delete child rows

texts:
I have a Product database with the following components:

ProductOptions is a list of option names for each Product, ProductVariants is a list of variants for each Product and ProductVariantOptions is the 1-to-many list of the option values for each variant and option name.

ProductVariantOptions contains the following:

ID is row ID.
VariantID is product's variant ID.
OptionID is product's option ID.
Value is a string with the option's value.

So for a product with Size and Colour options, each variant must contain two rows, one specifying the size and one specifying the colour.

== Variants Table ==
ID = 1, ProductID = 9, Quantity = 23, BackOrderEnabled = 1
ID = 2, ProductID = 9, Quantity = 10, BackOrderEnabled = 1

== Options Table ==
ID = 1, Name = "Size"
ID = 2, Name = "Colour"

== VariantOptions Table ==
Variant = 1, Option = 1, Value = "10"
Variant = 1, Option = 2, Value = "Red"
Variant = 2, Option = 1, Value = "10"
Variant = 2, Option = 2, Value = "Blue"

Is it possible to ensure that child rows in the VariantOptions table can only be deleted when either of their parents are? In other words, I'm not allowed to explicitly delete a row in that child table, only when the parent rows perform their cascade delete.

I need all child rows to exist unless either Options or Variants are deleted, causing their foreign keys to cascade.


texts: Can Indexes mitigate untrusted Constraint

texts:
I using sp_blitz and found two tables that have foreign keys that is untrusted. tried to enable the trust with:

ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName;
GO

But got this error message

  Msg 547, Level 16, State 0, Line 1
  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_importsessionrow_importsession". The conflict occurred in database "DatabaseName", table "dbo.importsession", column 'importsessionid'.

So I went and talked to the developers team and they told me that this ok and its how it should look like. Then I just inform them that it could have a huge performance impact on queries. So the question I went back with from Developer team is:

  Can this be mitigated with indexes?

I would be very pleased if you can help me answer the Dev team

Regards
Håkan


texts: SQL Server: get meta-Info about LDF-File

texts:
I am trying to get the timestamp of the first entry in the .LDF file of a live database.
Is there any way to do this?

I already get the first LSN in the LDF using

SELECT MIN([Current LSN])
FROM sys.fn_dblog(NULL,NULL)

This returns a string like 00000064:000000e9:0033. But how can I convert this into a timestamp? The function sys.fn_cdc_map_lsn_to_time throws an error that "cdc.lsn_time_mapping" is not defined. This is not surprising IMO, since CDC is not enabled on the server.

BTW: This is NOT a duplicate of my own question 83446 from last november: I don't have a TRN to parse, but only the .LDF.


texts: SELinux is preventing pg_ctl from read access on the file postgresql.conf

texts:
I am trying to setup a master-slave PostgreSQL 9.4 database on Fedora 22, with the slave database's PGDATA being different from the default /var/lib/pgsql/data/ directory. I wrote this to /etc/systemd/system/postgresql.service

.include /lib/systemd/system/postgresql.service
[Service]
Environment=PGDATA=/mnt/db/data

and ran

semanage fcontext -a -t postgresql_db_t "/mnt/db/pg_data(/.*)?"

However, the database won't start and running journalctl -xe gives the title error. How would I go about fixing it.


texts:
We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic.

The logic is generally simplistic:

The stored procedure contains an INSERT (or UPDATE) statement against a view, which has INSTEAD OF INSERT/UPDATE triggers
The trigger contains the actual logic -- usually two statements, dealing with INSERT/UPDATE of a record into a main table and one (or more) supporting tables (incl. referential integrity).

The problem is when something like a CHECK constraint is hit within the statement in the trigger. The transaction gets dumped, and everything is rolled back. I'm looking at having to duplicate the CHECK/etc constraint in the offending stored procedures to validate prior to the trigger executing -- is there any alternative?


texts: Best practices for keeping protobuf spec in sync with relational database (e.g. PostgreSQL)

texts:
1. We have a schema definition in Google's Protocol Buffers format that we use to generate code for different gRPC implementations (Python, PHP)

2. We have a database (PostreSQL) that is initialized from SQL files which map the Protocol Buffers structure.

Problem: Frequently, the protobuf spec changes. This must be reflected in the database, which is a tedious task and prone to mistakes when done manually.

We recently moved to Alembic Migrations for migrating continuous updates to the (live) database structures when we update the Protocol Buffers specification. However, we still feel like we need to maintain two structure that are basically equal or at least very similar: ProtoBuf and SQL-Code (managed via Alembic Migrations or in plain *.sql files).

I have searched over and over and there is just very little information on how others solve this problem. Ideally, we would like to set up a CI/CD process that converts the protobuf specification (e.g. a message name changed from teespoon to knive) to an SQL Schema Definition (e.g. CREATE TABLE teespoon (...);) or creates SQL Migrations Code from it (e.g. ALTER TABLE teespoon RENAME TO knive;).

I know it is more complicated in reality than with my simple examples, but I can't even find code that supports half-automated and human supervised conversion. Any advice?

What I have found so far:

from SQL files, we can create a PostgreSQL DB (e.g. in Docker) and use sqlacodegen to automatically create an SQLAlchemy Model
from the SQLAlchemy Model, we can use Alembic Migrations to create (half)automatic Schema Migration Code
we're back to point zero: we need to maintain the base SQL files and protobuf spec
there is Mercator, but as far as I understand it can only map from SQL to gRPC, not vice versa
there's also migra, which looks excellent for generating auto-diffs for different PostgreSQL schemas (here's a presentation and the docs)

Maybe there could be something like "Protobuf Migrations", where one can define SQL Migrations for each protobuf spec change?


texts: With Galera now somewhat supporting MyISAM replication, does that also mean that it will replicate Aria databases with storage engine (which is more or less feature complete to MyISAM, and a drop in replacement). Are there any special configs I need to watch out for to make this work (such as wsrep_replicate_myisam=ON) ?


texts: I want to get all the weeks of a month with the start and end dates of those weeks without creating a table


texts:
I'm trying to get my cube working without getting "attribute key not found" errors, but the only way I've found to make it work, is to set up a referenced dimension from Dim_PropertyInfo to Dim_SalesInvoiceHeader (see scripts below) and leave the Materialize checkbox unchecked.

However, when I browse the cube, filter on INV001 and display the PropertyInfo attribute, it still returns all rows from Dim_PropertyInfo.

Can somebody tell me what I should be doing?

Script to create the test tables:

CREATE TABLE dbo.Dim_SalesInvoiceHeader
(
    SalesInvHeaderID    INTEGER         NOT NULL,
    SalesInvoiceNumber  NVARCHAR(50)    NOT NULL,
    OtherStuff          NVARCHAR(MAX)   NULL,

    CONSTRAINT PK_Dim_SalesInvoiceDetails
        PRIMARY KEY (SalesInvHeaderID),
);
GO

CREATE TABLE dbo.Fact_SalesInvoices
(
    FactID              INTEGER NOT NULL,
    SalesInvHeaderID    INTEGER NOT NULL,
    LineItem            INTEGER NOT NULL,

    CONSTRAINT PK_Fact_SalesInvoices
        PRIMARY KEY (FactID),
    CONSTRAINT FK_Fact_SalesInvoices_Dim_SalesInvoiceHeader
        FOREIGN KEY (SalesInvHeaderID)
        REFERENCES dbo.Dim_SalesInvoiceHeader (SalesInvHeaderID),
);
GO

CREATE TABLE dbo.Dim_PropertyInfo
(
    PropertyInfoID      INTEGER         NOT NULL,
    SalesInvHeaderID    INTEGER         NOT NULL,
    SalesInvoiceNumber  NVARCHAR(50)    NOT NULL,
    PropertyInfo        NVARCHAR(MAX)   NULL,

    CONSTRAINT PK_Dim_PropertyInfo
        PRIMARY KEY (PropertyInfoID)
    CONSTRAINT FK_Dim_PropertyInfo_Dim_SalesInvoiceHeader
        FOREIGN KEY (SalesInvHeaderID)
        REFERENCES dbo.Dim_SalesInvoiceHeader (SalesInvHeaderID)
);
GO

... and the script to populate the tables:

INSERT INTO dbo.Dim_SalesInvoiceHeader
    ( SalesInvHeaderID
    , SalesInvoiceNumber
    , OtherStuff
    )
    VALUES (1, N'INV001', N'blah')
         , (2, N'INV002', N'blahblah')
         , (3, N'INV003', N'blahblahblah')
         , (4, N'INV004', N'stuff');

INSERT INTO dbo.Fact_SalesInvoices
    ( FactID
    , SalesInvHeaderID
    , LineItem
    )
    VALUES (1, 1, 1)
         , (2, 1, 2)
         , (3, 1, 3)
         , (4, 2, 1)
         , (5, 2, 2)
         , (6, 3, 1)
         , (7, 3, 2)
         , (8, 4, 1)
         , (9, 4, 2);

INSERT INTO dbo.Dim_PropertyInfo
    ( PropertyInfoID
    , SalesInvHeaderID
    , SalesInvoiceNumber
    , PropertyInfo
    )
    VALUES (1, 1, N'INV001', N'stuff')
         , (2, 2, N'INV002', N'pool');


texts: Db2 - How to configure monitoring to get all of the SQL statements and authorization IDs for specific table?

texts:
On Db2 v11.1 on Linux I have a requirement to collect all of the SQL statements and database authorization IDs that access 5 particular tables.
I am trying to configure "event monitor", but can't figure it out how to specify condition to only collect statements per table. Is it possible to somehow restrict collecting statements/authorization IDs per table?
Or is there any other way to get this info?
Regards


texts: Is there a way to set up DNS to point to Always-On Read-Only node w/out ApplicationIntent?

texts:
I know that we can use ApplicationIntent=ReadOnly to connect to the secondary read-only nodes in an AG setup. Is it possible in some way to tell the DNS server which node is which on failover so we can just point an alias to the read-only node and not have to use a different connection string? I realize this might take some handling in events or something similar, but it seems like this would be useful to give people an alias to use for reads.

I realize that it wouldn't be correct 100% of the time, especially right after a failover, but wondering if this is possible in some way.


texts: Is REPEATABLE READ the right choice for validation before modifying data?

texts:
In my database I have a Task table. This table has a number of fields that are foreign keys to other tables. There are also a few link tables, that have Task on one end.

When a new task is created (or an old task is updated) I need to make sure that these foreign keys exists, and return a meaningful error if they do not. This is important because the call originates from a web api, so consumers really need to know why their call did not work, so a detailed error message (without giving out too much) is crucial.

Since there are a dozen of foreign keys (and also some other conditions, like current user application account being active) that make task correct, instead of trying to insert and failing I'm trying to validate these before I insert.

Of course when I do this I need to make sure that when I actually insert the record these validations still hold true.

So let's assume a task references a group. From the web api perspective group is a reference data, and it returns a list of valid group keys in a prior call. It receives one of them back when it creates or updates a task as a GroupId field on the Task object.

To make sure that this (and other) foreign keys are present in the database I open a REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message.

The question is if REPEATABLE READ isolation level appropriate in this case. Paul White mentioned that often it is not .


texts:
I have a RDS instance running in AWS. By default

  max_connections=45

so I used mysqlslap to see how it works. I ran:

mysqlslap --concurrency=20 --host=deleteme.ckn5fsf3nwqp.ca-central-1.rds.amazonaws.com --user=deleteme --password --auto-generate-sql

and it worked nicely. Then I modified the --concurrency to 45, 50, 100, 150, 200 and it worked anyway, I was expecting a 'Too many connections' error, but I only see that error from --concurrency to over 300.

So my 1st question is, if my max_connections is set to 45, and using a concurrency of ~200 connections, how doesn't mysqlslap give me error?

I also tried this experiment running mysql in an EC2 instance. I tried --concurrency with 500 connections, and got the 'Too many connections error', but that error is not logged in /var/log/mysql. Is it a default configuration that mysql doesn't log those errors?

and my last question is again regarding RDS. I was hoping to see in cloudwatch, a spike on DB connections, but even using mysqlslap at concurrency=45, I only see 'DB Connection Count' metric to 1.

Maybe mysqlslap concurrency does not create new connections?


texts: Which client authentication methods make sense when calling `initdb`?

texts:
Postgres offers many client authentication methods:

Trust Authentication
Password Authentication
GSSAPI Authentication
SSPI Authentication
Ident Authentication
Peer Authentication
LDAP Authentication
RADIUS Authentication
Certificate Authentication
PAM Authentication
BSD Authentication

The client authentication method flags on initdb:

-A or --auth
--auth-host
--auth-local

…specify the default authentication method for users used in pg_hba.conf.

I am making a Wizard tool for creating an initdb string, the string to be executed by someone setting up a new Postgres cluster. This led me to contemplate all the possible values for authentication with initdb.

➥ Of the list above, which are practical/sensible for use with initdb?

Obviously trust is the simplest way to get started, though usually best to change soon after the cluster is established.

Some are not practical. For example, PAM authentication requires that the user must already exist in the database. So PAM cannot be used for setting up a new cluster with initdb.

I am not familiar with several of the other authentication methods. And I am no security expect. This leaves me wondering: Which of the client authentication methods could be used practically/sensibly with initdb?


texts: Updating mongo sharded cluster without down time for mongod

texts:
I am trying to update my mongo sharded version from 2.4 to 2.6.3.

I have some queries on steps to update this.

When I will run one mongos with the --upgrade option, will it update all the config and mongos or do I have to manually replace binaries on each one of them and restart the mongos and config?
How can I update my config servers without downtime?
Upgrading shard needs to down its members one by one and update the binary. Is there any other way to do it?


texts: Create 7 day rolling average in PostGIS for each feature


texts: Why does MySQL Workbench let me add unique indexes to primary keys?

texts:
I'm using MySQL Workbench 8.0.12
Reproduce:

Create a table
Create the first column id, select it as a primary key (PK)
Under indexes, PRIMARY appears
Back under columns, also select id as unique (UQ)
Under indexes, id_UNIQUE appears

Confusion:

From reading MySql - Is primary key unique by default?, I understand that the primary key implies uniqueness by default. This is in line with how the guy in this video skips the UQ after checking the PK. Finally, this answer confirms having both PK and UQ on the same column is redundant:

If you declare a column as UNIQUE as well as PRIMARY KEY, MySQL (Workbench?) oddly creates a second index over the same columns. This is truly redundant and only takes up extra storage space and causes a bit of overhead when you insert/update/delete rows. It doesn't give any benefit.

If this truly is detrimental for performance, and from my previous experience with MySQL Workbench double-checking and cross-referencing whatever I had been doing, I would imagine the developers should have made it fool-proof by now to not let users select UQ on top of PK. For example, communicate by disabling the UQ checkbox on a column already having the PK selected. Since they didn't, is it because there could be a scenario where someone may actually need both PK and UQ on the same column?

To make this more confusing, this (upvoted) answer says:

Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an index which happens to be unique, for instance because you add the primary key to it.

Which implies that these are two independent states. However, that contradicts the MySQL Workbench behaviour, where checking UQ on a column will automatically create an index, and if you go ahead and delete that index, it automatically deselects the UQ.

So could there be edge cases, or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?


texts: Filter ingredients choices for recipe database by category, or dynamic search?

texts:
Requirement:  There are thousands of ingredients in given commercial kitchen.
To simplify user interface,  when entering a new recipe, only ingredients relevant to the Food Category selected by user should be shown.

For example, when entering a new cookie recipe in a form, show only ingredients used in generally used in Cookies recipes

Simple Solution (?):
A RECIPE belongs to one  FOOD_CATEGORY.  For example: Peanut Butter Cookie ---> Cookies
INGREDIENT <- ManyToMany -> FOOD_CATEGORY

Will this suffice to meet the above requirement, and are there any "gotchas"?

Perhaps a simpler solution is to use a Ajax style dynamic search box that filters all ingredients as you begin to type.

More broadly, is there a methodology that drives Database design from the UI to prevent any potential surprises?

For example, after my initial design, I realized a MultiSelectBox with too many ingredients simply wouldn't work.


texts: Will DROP TABLE of an empty table with a large tablespace file cause a long lock?

texts:
Let's say I have a reasonably large (100GB) INNODB table on a 3-node Percona Xtradb Cluster that uses innodb_file_per_table=1 and has about 80GB buffer_pool per node and uses ext4. I want to drop this large table without causing any global lock. I use this approach:

I delete all the rows from the table with pt-archiver. At this point the table is empty, but the innodb file for that table is still ~100GB.
I issue a DROP TABLE tablename

Will I get any global lock due to either ws-rep or the fact that Innodb has to go through the LRU list and discard the pages which belong to this tablespace?


texts:
How can i disable the GSSAPI plugin? I do not need authentication via Kerberos on my server.

This is my environment:

Centos 7.3 with SELinux mode enforcing
MariaDB Server: 10.1.19

In my log file /var/log/mariadb/mariadb.log i see the next errors:

[Warning] GSSAPI plugin : default principal 'mariadb/locahost.local@' not found in keytab
[ERROR] mysqld: Server GSSAPI error (major 851968, minor 2529639093) : gss_acquire_cred failed -Unspecified GSS failure.  Minor code may provide more information. Keytab FILE:/etc/krb5.keytab is nonexistent or empty.
[ERROR] Plugin 'gssapi' init function returned error.

I cannot find any related to that plugin in the configuration:enter code here

> SELECT PLUGIN_NAME FROM information_schema.PLUGINS WHERE PLUGIN_TYPE = 'AUTHENTICATION';
+-----------------------+
| PLUGIN_NAME           |
+-----------------------+
| mysql_native_password |
| mysql_old_password    |
+-----------------------+

> SELECT * FROM mysql.plugin;
Empty set (0.00 sec)

I only found this configuration file:

/etc/my.cnf.d/auth_gssapi.cnf


texts: Automatically import .bak file on a daily basis

texts:
I have a piece of industrial equipment running SQL Server with a database named, let's say, "primary". The equipment creates on a daily basis a .bak file (of the full database) and it transfers it to a server running SQL Server. On the server, I need to have an ARCHIVE of "primary".
My idea is to create a daily job to import only the new records from the .bak file, but I have no idea where to start from.
I have looked online for "importing .bak into SQL Server" but all I find is about the RESTORE command which I'm afraid doesn't work in my case.
Can anyone point me in the right direction?
UPDATE 1 - According to the warranty given to us by the manufacturer of the equipment, we can't interact in any way with the embedded SQL Server. The only way to access the data is via the .bak file.


texts: What am I doing wrong in mysql database?

texts:
I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message:

  MYSQL ERROR: Can't update table 'player_viplist' in stored
  function/trigger because it is already used by statement which invoked
  this stored function/trigge

What do I need to do for this trigger to work?

DROP TRIGGER dele;

DELIMITER |

CREATE DEFINER = 'root'@'localhost' TRIGGER dele
  AFTER INSERT
  ON player_viplist
  FOR EACH ROW
BEGIN DELETE FROM player_viplist ;

END|

DELIMITER ;


texts:
I wrote this query:

UPDATE authors
SET best_seller =
    (
    SELECT TOP 1 book_id
    FROM books
    WHERE books.author_id=authors.author_id
    ORDER BY copies_sold DESC
    )

I would like the SELECT to be evaluated once for every row in books, so that every author record contains his/her most sold book. Let's ignore:

Performance
The bad idea of storing something that could be computed in other ways
The case that there is a tie between 2 books of the same author

Do you see any other problem with the query, or will it work as expected in all cases?


texts: Why does Oracle hang on "ALTER TABLE ADD ... NOT NULL"?

texts:
Using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0, the operations below have completely stumped me. The first locks up (table has DDL lock, finally killed query after 20+ minute wait). The second does not.

###This hangs:
ALTER TABLE customer ADD primary_seg varchar2(9) NOT NULL;

###This does not:
ALTER TABLE customer ADD primary_seg varchar2(9);
ALTER TABLE customer MODIFY primary_seg NOT NULL;

There are no records in the table, there are no other users than myself on the instance, and I have no other queries executing which might cause a lock.  The table previously had a large number of rows that were removed via TRUNCATE TABLE.   There are no foreign keys or other referential constraints.


texts:
Is there a way to prevent SSMS 2012 from adding the word 'NULL' to CSV exports?

Steps to reproduce behavior:

Create a new query (Ctrl+N)
Select Query | Results To | Results to Grid (Ctrl+D)
Run query: SELECT getdate() NOW, NULL DUMMY; results in one row and two columns, the second column displays ‘NULL'
Right click the results pane and choose Save Results As…; save the file as CSV
Open the file with Notepad (view the ‘raw’ values)

Actual result:

NOW,DUMMY
2015-10-16 10:25:28.403,NULL

Desired result:

NOW,DUMMY
2015-10-16 10:25:28.403,

Is there a setting that will produce the desire result?  Do newer versions of SSMS work differently in this regard?


texts: Foreign keys become untrusted after bulk insert

texts:
In a SQL 2014 edition server (12.0.2430.0 - no SP1 yet)
with a database in 2012 compatibility mode (working on getting it switched to 2014...) I have a handful of foreign key objects which are consistently marked as not trusted in the database. I've dropped and re-created them without NOCHECK options, but within 5-10 minutes they become untrusted again and if I generate a CREATE script it comes out as:

ALTER TABLE [dbo].[Points]  WITH NOCHECK
ADD  CONSTRAINT [FK_BadgeId] FOREIGN KEY([BadgeId])
REFERENCES [dbo].[Badge] ([Id])
GO

The create script being used is:

ALTER TABLE [dbo].[Points]
ADD  CONSTRAINT [FK_BadgeId] FOREIGN KEY([BadgeId])
REFERENCES [dbo].[Badge] ([Id])
GO

ALTER TABLE [dbo].[Points] CHECK CONSTRAINT [FK_BadgeId]
GO

There is no replication, there are no third party tools, and I am monitoring all DDL statements on the database so it isn't another user.

I am able to check the constraints fine (using WITH CHECK CHECK on each) but they still become untrusted shortly after. Only maintenance jobs that run are Ola's in the early AM and this happens throughout the day.

Update:

So, after a couple of traces to narrow down possibilities, it seems like a BULK INSERT may be causing the FK to become untrusted. This msdn question states that this is a valid route for the key to become untrusted, which is the first I've heard of it.

So my question now is, is there an alternative strategy to using BULK INSERT that can maintain foreign key is_trusted status? It's being executed in the context of an application running several times per hour. I could have the developers batch their insert statements instead, but I'd prefer not to put an ultimatum on using BULK INSERT if I don't have to.


texts: SQL Server Management Studio Change User Server Roles to sysadmin?

texts:
On my local machine I have SSMS (SQL Server 2008 R2).

For user xxxx Server Roles are only public.

When I try to change it to sysadmin it throws the error:

  User does not have permission to perform this action. (.Net SqlClient Data Provider)
  Error Code 15247

Server type (local)/SQLExpress
Authentication: Windows Authentication
User: ComputerName\username (disabled)
Password: none (disabled)

How can I do this?

The user I am connecting with server role is: public, but Windows Admin user.

The instance is configured for mixed mode authentication, but I don't remember the password for sa. I can't change the password because I am not sysadmin.

Is there any way I can create a sysadmin user account or make my account sysadmin and get full control of my SSMS?


texts: Restoring SQL Server 2012 Enterprise to Web Edition not possible due to full text partitioning

texts:
I have an SQL Server 2012 Enterprise database I’m trying to restore to SQL Server 2012 Web Edition instance. I’ve used sys.dm_db_persisted_sku_features to find features to remove before a backup/restore down edition will work. I found partitioning and compression which I removed but sys.dm_db_persisted_sku_features still tells me partitioning exists.
After looking closer I found several partition schemes and functions with names like ifts_comp_fragment_data_space_xxxxxxxx and ifts_comp_fragment_partition_function_xxxxxxxx respectfully which are set to is_system=1. When trying to remove them I get the error “Cannot drop the partition scheme 'ifts_comp_fragment_data_space_ xxxxxxxx ', because it does not exist or you do not have permission.”

Apparently these are automatically created when full text indexes become large. I’ve removed all full text catalogs but the schemes and functions remain. Thinking they may not matter I tried the backup /restore. Within the restore error message was “Database '' cannot be started in this edition of SQL Server because it contains a partition function 'ifts_comp_fragment_partition_function_xxxxxxxx".

I’ve only found one solution here which forces the objects is_system property to 0 but it comes with a disclaimer that the solution probably isn’t supported by Microsoft. Assuming this would disqualify us for support in the future this is not an option for me.

Is there any other way around this problem? A way to remove these partitioning schemes and functions?