Is using MySQL views a good translation mechanism? We want to use a foreign vendor MySQL database, but the whole schema is barely understandable by our development team. We could alter the schema to rename tables and columns once for all, but the database is periodically updated and reinstalled on our production servers.

Would building isomorphic views be a good translation mechanism in our case? We would use our locale in application queries and yet be able to refer to native locale in case we wish to interact with the database vendor.

As we would have strictly the same tables and columns, would there be any performance alteration, in particular with regards to existing indexes?

Views are a good way to accomplish what you want to do. If your views take advantage of existing indexes or are 1:1 against the underlying tables, then queries against them will use the indexes. Since you're expecting periodic updates, you'll want to script the view creation, and in that same deployment script you can always add indexes if you need more.

You will want a 1:1 view of the columns for each table that simply performs the translation on the column and table names, enabling updates and inserts on those views in your locale. If the database contains stored procedures, you can create wrappers for them in your native language by adding new procedures to accept translated parameters and call the original procedures.

One caveat is the collation used by the database: if it is not suitable for your locale, using COLLATE to a different character set in your SQL statements will negatively impact performance.


Yearly database archive design I need to implement a DB of tracking numbers. The tracking numbers table could potentially contain billions of records during its lifetime so an archive database should be implemented to improve future performance and maintenance costs.

I'm thinking of prepending the year in the tracking number, say 2019-XXX for all tracking numbers in the year 2019, then 2020-XXX for the year 2020 and so on.

The tracking numbers are then stored on their respective year table e.g., TrackingNumbers_2019, TrackingNumbers_2020, and so on.

Every new year, the previous year's table will be dropped from the "active" DB then moved to an "archive" DB. The archive DB will be rarely queried. It will be on a lower-end machine with read-only mode. It will contain all previous years data.

Now in case somebody wants to search for a specific tracking number, the application will determine which DB/table it's going to look on based from the year prefix. E.g., If the current year is 2019, then a search for 2019-XXX will lookup the active DB, but a search for 2018-XXX will lookup the archive db.

Do you think this design is sound or is there a better way?

I wouldn't normally prefix the tracking number with a year, instead I would have a separate field with a date in it. This way you can use BIGINT and the indexing would go a little faster than using something like VARCHAR. If having the year in it is better for human reading, then the application can always display those two numbers together. BIGINT can store more than 9 quintillion values, assuming you only use positive numbers. This could mean that depending on your equipment and performance, you might not need to archive but every few years, instead of every year.


Is ANALYZE useful immediately after creating an InnoDB index? Let's create a new index on an existing table:

CREATE INDEX `IDX_some_index`
    ON `some_table` (`a_column`, `another_column`);

Is this useful?

ANALYZE TABLE `some_table`;

Can anyone point to some documentation clarifying whether the CREATE INDEX process analyze the target table or not?

CREATE INDEX does not issue ANALYZE TABLE.

When it comes to InnoDB, running ANALYZE TABLE is kind of useless. Why ?

I wrote this post From where does the MySQL Query Optimizer read index statistics? 2.5 years ago.

In it, I explained that InnoDB can overwrite statistics by means of just running queries. The Query Optimizer will do depth searches in BTREE indexes and take guesses at the cardinality of index column combinations. In a relatively short time, I would say that index stats can get stale. This applies to older versions of MySQL 5.

From the ANALYZE TABLE Documentation for MySQL 5.6, InnoDB's stats are persistent by default. Thus, running ANALYZE TABLE on InnoDB tables for MySQL 5.6 actually means statistics are computed and will only deteriorate via heavy INSERTs, UPDATEs, and DELETEs.

As for you question, I say : CREATE INDEX does not issue ANALYZE TABLE automatically.


What are some important security-related error codes / events to monitor? I am setting up alerts in SQL Server and am interested in what events I should be raising alerts for.

In particular, I am looking for any events related to security.

I am already monitoring error 18456.

It's a good idea to create SQL alerts in the SQL agent for all errors of severity 16 through to 25. Also create 1 SQL alert for each of these error IDs 823 824 825.

I would create an operator that points to an AD group (probably DBA or ITsupport) so that a team can be alerted or you can drop users in and out of that group when you are away so that errors are not missed.

This will keep you in the loop when things are happening on your server that you should know about but something like Redgate SQL Monitor is a much better solution.


ST_Distance_Spheroid does not exist I've try use MySql function ST_Distance_Spheroid for calculating distance but got following error :

  ST_Distance_Spheroid does not exist.

Is something missing?

ST_Distance_Sphere() is special optimization on a sphere. If you want to calculate using an spheroid just use ST_Distance(). From the docs on ST_Distance()

  ST_Distance() detects arguments in a geographic (ellipsoidal) spatial reference system and returns the geodetic distance on the ellipsoid. The only permitted geographic argument types are Point and Point, or Point and MultiPoint (in any argument order). If called with other geometry type argument combinations in a geographic SRS, an ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS error occurs.

An ellipsoid is a spheroid where two axes have the same length.


MongoDB Storage size scaling I have a Java + MongoDB web application deployed on an OpenShift production medium gear, with no scaling enabled, with 11GB storage.

At the moment, I have some data stored, so MongoDB accurately reports a Data Size of 720.4K. This grows appropriately when I add new objects, everything is ok.

What I am concerned about is the reported Storage Size of 1.52M. Since this is a production gear, I am expecting that this allocated storage space will grow as needed (at the current rate of data production, I estimate the 1.52M limit will be reached in a few days). Is my expectation correct?

ETA: In the meantime, the Storage Size has increased to 2.02M, so I guess that answers my question.

You can more or less estimate that but you cannot be 100% sure.

A piece of advice I can give you is to change your storage engine to wiredTiger. I had 20.0GB of disk used by a mongodb instance using the MMAPv2 (which was the default storage engine before Mongodb 3.2). When I converted that to use wiredTiger my disk space dropped to unbelievable 1.9GB.

That might help you for a while.


What is the foreign key between two children in the same hierarchy? I'm trying to map an entity relationship design into a relational database. Under STAFF entity, I have 2 children are BUSINESS MANAGER and EMPLOYEE.

My teacher requests to add a 1-M relationship SUPERVISE between Business_MGR and EMPLOYEE (a BUSINESS-MGR supervises 0, 1, or more employees, an EMPLOYEE is always supervised by one BUSINESS-MGR).

What is the foreign key for the EMPLOYEE entity?

The id of a manager that you store in the supervisor column of employee.


Deadlock scenario, where is this S lock coming from? I am encountering the below deadlock scenario. Please see the attached object definitions and execution plans. The plans were pulled out of cache from a system where this deadlock does occur.

I understand what all of this data is telling me but what I am confused about is why the spUpdateUserAccount stored procedure is taking an S lock on IX_Person_TaxPreparer_rowCreated. This lock appears unnecessary and there is no reference to IX_Person_TaxPreparer_rowCreated in the execution plan for spUpdateUserAccount.

...

Full question, with attachments, posted on SQLPerformance

I'll repost my response on twitter here:

Based on the deadlock XML you posted, which lists a trancount of 3 in the session that invokes spUpdateUserAccount, a prior statement or batch in that transaction likely put the lock on IX_Person_TaxPreparer_rowCreated.

I'm presuming this lock is held because of the foreign key constraint in place.

The locks are held for the duration of the transaction, so your second statement can't complete til the first commits the open transaction.


Querying for a specific language and if not found use default one instead I currently store all language data (including default language) in my database. Generally a simple query making the assumption the language exists is as simple as doing:

SELECT a.artifact_id,
       a.base_atk,
       a.base_hp,
       a.max_atk,
       a.max_hp,
       b.name,
       c."class",
       b.lore_description,
       b.skill_description
FROM GameArtifact a
JOIN GameArtifactNameDesc b
    ON a.artifact_id = b.artifact_id AND b.locale = 'en-US'
JOIN GameClassTranslation c
    ON c.identifier = a."exclusive" AND c.locale = 'en-US';

And a result from that said query:

-[ RECORD 1 ]-----+-------------------------------------------------------------------
artifact_id       | abyssal-crown
base_atk          | 15
base_hp           | 54
max_atk           | 195
max_hp            | 702
name              | Abyssal Crown
class             | Mage
lore_description  | May those ruled by the crown walk the road of corruption forever. +
                  | - Witch Reinwood, hung high up on the stake
skill_description | 12.0% (24.0%) chance to stun the enemy for 1 turn after attacking.

The issue is, what if the language (which is set on the application) is not found? How can I set it to use a default language (let's say: es-AR) and query for that language? Or what if there's translation for that language in one table but not in both?

I thought of two approaches:

Approach #1

Doing the query presented above and if it doesn't return anything, query again using the default language which I can be certain there's data of in the database.

This doesn't sound convincing and nice to do.

Approach #2

Cut off the short tables and do their translations on the application. Which in this case GameClassTranslation, which only contains 49 rows, 7 languages so 7 rows per language but which might expand in the future.

This also doesn't sound convincing from the point of keeping everything in one place rather than split everywhere.

Is there any better solution for this?

Giving next example:

create table a (id int, description text);
create table b (id int, lang text, description text, default_lang int);
insert into a values (1, 'Yo vivo en Barcelona');
insert into b values
(1, 'en-US', 'I am living in Barcelona', 0),
(1, 'es-AR', 'Yo vivo en Barcelona', 1);

If you table have a default text for the default language you could use a LEFT JOIN and COALESCE in this way:

select
    a.id,
    coalesce(b.description, a.description) as description
from
    a
left join
    b
    on b.id = a.id and b.lang = 'en-US';

id | description
-: | :-----------------------
 1 | I am living in Barcelona

Or you could add a default_lang column in translations table and use a LATERAL JOIN with LIMIT 1:

select
    a.id,
    coalesce(b.description, a.description) as description
from
    a
join lateral
    (select id, description
     from   b
     where  lang = 'en-EN' or lang = 'es-AR'
     order by default_lang
     limit 1) b on true

id | description
-: | :-------------------
 1 | Yo vivo en Barcelona

db<>fiddle here


MySQL script writing slow I'm using php and the exec() function to run 2 MySQL script files one after another

The first file has approx 2k update statements (see below) in and is 114kb in size.

UPDATE table1 SET is_active = 1 WHERE id = 43;

The second has approx 15k update statements (see below) in and is 817kb in size.

UPDATE table2 SET is_active = 1 WHERE id = 12345;

The id column in table1 and table2 is a primary key so I would've thought this should be quite a quick update.

When I watch the MySQL Workbench dashboard for number of updates per second the first file runs at approx 80/s and the second 5k/s.

This means the smaller file with less updates is running much slower than the larger one.

Does anybody know if there is a logical reason for this?

I've realised that using an alternative query structure helps to speed this up.

UPDATE table1 SET is_active = 1 WHERE id IN (1,2,3,4,...)

Works much quicker and reduces the script file size to 11kb from 114kb and also reduces running time from 40 secs to just under 2 secs


How to turn off identity for a database column of a table I have mistakenly deleted a table in one one my database server. I have same set of data in the other server.

What I have done is clicked on edit top 100 rows for the table, then copied it and pasted in the other server table.

But , here the problem is : one of the columns of the table is an identity. So, i was unable to copy that column here.

I need same identity values because these values are referred in the application, even if i copy the data and update it, new id values are being generated.

Is there any way to turn off identity for that column, paste these values and again turn on identity.

If I understand you correctly, try this one -

SET IDENTITY_INSERT <your_table> ON

INSERT INTO ... (ID, ...)

SET IDENTITY_INSERT <your_table> OFF


handler socket on tokudb engine I am trying to setup handler socket to work with tokudb engine. Database I am running is percona server and i have got tokudb enabled and the tables all have tokudb engine.

I was wondering if anyone knows handler socket is compatible with tokudb aswell.

Any advise on this would be appreciated.

Thanks

MariaDB documentation page for HandlerSocket says:

HandlerSocket gives you direct access to InnoDB/XtraDB and SPIDER.

It doesn't mention TokuDB (despite it being distributed with MariaDB), so I would exclude that this engine is supported.


Updating a column name to be a concatenation based on the same table Need Help in SQL SERVER!
I have two tables: Students: Student_Id, Student_Name
Grades: Student_Id, Profession, Grade.
I need to change the column Profession to have the value Profession + Grade only for those students who have more than one profession. Is it possible?
I tried:
UPDATE Grades SET profession = CONCAT(profession," + ",grade) WHERE student_id IN (SELECT student_id FROM Grades GROUP BY student_id HAVING COUNT(profession)>1);
Results
Query Error: Error: ER_UPDATE_TABLE_USED: You can't specify target table 'Grades' for update in FROM clause

I am not quite sure if I understand your data model from your description, so please check the data to be updated before running it.
You can break the update into two separate statements with a temp table or CTE, which will give you the benefit of seeing the records you want to update. (Use a select from the temp table or CTE beforehand to verify the records found.)
Example
-- Load a temp table with students to update
DROP TABLE IF EXISTS #X

SELECT
student_id

INTO #X
FROM
Grades
GROUP BY
student_id
HAVING
COUNT(DISTINCT profession) > 1 -- Assuming they could have multiple grades.

UPDATE G

SET profession = CONCAT(profession," + ",grade)

SET
FROM
Grades G
INNER JOIN #X X
ON G.student_id = X.student_id

WHERE
X.student_id IS NOT NULL -- Always helpful to have WHERE clause to avoid any accidents.


Sizing the SQL Agent Job Activity Monitor screen Does anyone know how to reset the initial (default) window size that is used when opening the SQL server Agent Job Activity Monitor screen?
When opening the Job Activity Monitor we always need to drag the window open to see the relevant columns.
Can this not be set/saved to always open at the width that I want?

Can't be done.

This is the additional 16 characters required to post an answer.


Percentage calculation with case not working as expected I've been racking my brain on this since yesterday afternoon, I figure it is time to call on those smarter than I. I have some data represented by the following. Sorry I have to use an image, I cannot figure out how to format to look like a table.

create table fico_scores
(
    fundingyear int,
    fico int,
    Correspondent varchar(50)
)

insert into fico_scores (fundingyear,fico,Correspondent)
        values (2017,593,'Amer Equit Mtg'),(2017,703,'Amer Equit Mtg'),
(2018,684,'Amer Equit Mtg'),
               (2017,691,'Amer Equit Mtg'),(2017,650,'Amer Equit Mtg'),(2017,697,'Am Corp'),
               (2017,652,'Am Corp'),(2017,792,'Am Corp'),(2018,0,'Am Corp'),
               (2018,796,'Am Corp'),(2017,729,'Appr Fun Corp'),(2017,748,'Appr Fun Corp'),
               (2017,780,'Appr Fun Corp'),(2018,770,'Appr Fun Corp'),(2017,663,'Appr Fun Corp'),
               (2018,752,'Appr Fun Corp'),(2018,715,'Cacl Risk Analytics'),(2018,683,'Cacl Risk Analytics'),
               (2018,735,'Cacl Risk Analytics'),(2017,693,'Cap Mtg LLC'),(2017,624,'Cap Mtg LLC'),(2018,746,'Cap Mtg LLC'),
               (2018,666,'Cap Mtg LLC'),(2018,669,'Cap Mtg LLC')

I Have the following code, and when running it looking at only one Correspondent everything looks fine as you can see below the code.

select fundingyear,case
                when fico = 1 then 'Less than 620'
                when fico = 2 then '620-679'
                when fico = 3 then '680-719'
                when fico = 4 then '720+' end as fico,
                sum(countfico) as count_Fico, count(countfico) * 1.0 /
           SUM(count(countfico)) OVER (PARTITION BY fundingyear) as fico_perc
FROM
(
    select fundingyear, case
                    when fico < 620 then 1
                    when fico >= 620 and fico <= 679 then 2
                    when fico >= 680 and fico <= 719 then 3
                    when fico >= 720 then 4
                    end as fico, count(fico) as countfico
    from vw_LoanRpt
    where Correspondent = 'Am Corp'
    group by fico,FundingYear
) t
group by FundingYear,fico

As you can see the percentages line up with the counts correctly.
But when I run the code to view all correspondents it looks really different.

My question is, what am I doing incorrectly that is causing such a skew in the calculations?

Again sorry for using images so much. I will learn this formatting stuff eventually.

TYIA!

                    2017          2018
Less Than 620      521 | 14%    499 | 13%
620 - 679         2027 | 28%   2619 | 31%
680 - 719         1260 | 21%   1661 | 20%
720+              2137 | 37%   3320 | 35%

Quick-n-dirty dual/CTE solution:

-- sum up the fico groups

with fico_groups as
(select   fundingyear,
          case  when fico <  620                 then '0-619'
                when fico >= 620 and fico <= 679 then '620-679'
                when fico >= 680 and fico <= 719 then '680-719'
                when fico >= 720                 then '720+'
          end as fico_txt,
          count(fico) as count_fico
 from     fico_scores
 group by fundingyear,
          case  when fico <  620                 then '0-619'
                when fico >= 620 and fico <= 679 then '620-679'
                when fico >= 680 and fico <= 719 then '680-719'
                when fico >= 720                 then '720+'
          end
),

-- get the yearly totals

fico_totals as
(select   fundingyear,
          sum(count_fico) as fico_total
 from     fico_groups
 group by fundingyear
)

-- final result set

select fg.fundingyear,
       fg.fico_txt                           as fico,
       fg.count_fico,
       (fg.count_fico *1.0 / ft.fico_total)  as fico_perc

from   fico_groups fg,
       fico_totals ft

where  fg.fundingyear = ft.fundingyear

order by fg.fundingyear, fg.fico_txt

Result set (based on sample data provided by OP):

fundingyear   fico      count_fico  fico_perc
2017          0-619     1           0,076923076923
2017          620-679   4           0,307692307692
2017          680-719   4           0,307692307692
2017          720+      4           0,307692307692
2018          0-619     1           0,090909090909
2018          620-679   2           0,181818181818
2018          680-719   3           0,272727272727
2018          720+      5           0,454545454545

Here's a db fiddle of the above.


RETURN value directly from INSERT with RETURNING clause I have a function that ends with:

    INSERT INTO configuration_dates (
      cols...
    ) VALUES (
      values...
    ) RETURNING id INTO ret_id;
    RETURN ret_id;

And I would like to remove the INTO ret_id part, and instead do something like:

RETURN (INSERT INTO configuration_dates (
  weekly_date_configuration_id,
  "from",
  "to",
  price,
  activity_configuration_id
) VALUES (
  wdc_id,
  from_ts,
  from_ts + wdc.duration,
  wdc.price,
  wdc.activity_configuration_id
) RETURNING id);

But I haven't found how to achieve this.

EDIT: adding entire function

CREATE FUNCTION make_date_from_configuration(wdc_id UUID, from_date DATE)
  RETURNS INTEGER AS $$
DECLARE
  from_dow INT := EXTRACT(isodow FROM from_date); -- day of the week (1 - 7)
  wdc weekly_date_configurations;
  from_ts TIMESTAMP;
  ret_id INTEGER;
BEGIN
  SELECT * INTO wdc
  FROM weekly_date_configurations wdc
  WHERE id = wdc_id;

  IF FOUND
    AND wdc.valid_through @> from_date             -- starts in the date range
    AND get_bit(wdc.weekdays, from_dow - 1) = 1    -- valid day of the week
  THEN
    from_ts := from_date || ' ' || wdc.start_time;

    INSERT INTO configuration_dates (
      weekly_date_configuration_id,
      "from",
      "to",
      price,
      activity_configuration_id
    ) VALUES (
      wdc_id,
      from_ts,
      from_ts + wdc.duration,
      wdc.price,
      wdc.activity_configuration_id
    ) RETURNING id INTO ret_id;
    RETURN ret_id;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;

You can simplify the whole function and use an OUT parameter, so the value is returned at the end of the function automatically:

CREATE OR REPLACE FUNCTION make_date_from_configuration(
          wdc_id uuid
        , from_date date
        , OUT ret_id int) AS
$func$
BEGIN
   INSERT INTO configuration_dates (
          weekly_date_configuration_id, "from", "to", price, activity_configuration_id)
   SELECT wdc_id
       , (from_date || ' ' || wdc.start_time)::timestamptz
       , (from_date || ' ' || wdc.start_time)::timestamptz + wdc.duration
       , wdc.price, wdc.activity_configuration_id
   FROM  weekly_date_configurations wdc
   WHERE wdc.id = wdc_id
   AND   wdc.valid_through @> from_date                        -- starts in the date range
   AND   get_bit(wdc.weekdays
               , EXTRACT(isodow FROM from_date)::int - 1) = 1  -- valid day of the week
   RETURNING id
   INTO  ret_id;  -- returned at the end automatically
END
$func$  LANGUAGE plpgsql;

All the variables and assignments and conditional expressions are just needless noise. Can be a single SQL command: shorter and faster.

You still need the INTO clause, though. (And I fail to see why you try to avoid that.)

Or you can use a plain SQL function instead, to return the value directly:

CREATE OR REPLACE FUNCTION make_date_from_configuration(wdc_id uuid, from_date date)
  RETURNS int AS
$func$
   INSERT INTO configuration_dates (
               weekly_date_configuration_id, "from", "to", price, activity_configuration_id )
   SELECT wdc_id
       , (from_date || ' ' || wdc.start_time)::timestamptz
       , (from_date || ' ' || wdc.start_time)::timestamptz + wdc.duration
       , wdc.price, wdc.activity_configuration_id
   FROM  weekly_date_configurations wdc
   WHERE wdc.id = wdc_id
   AND   wdc.valid_through @> from_date                      -- starts in the date range
   AND   get_bit(wdc.weekdays
               , EXTRACT(isodow FROM from_date)::int- 1) = 1 -- valid day of the week
   RETURNING id  -- return directly
$func$  LANGUAGE sql;


set session - custom variable to store user id I want to store user id in custom session variable and use it (read) in trigger procedures to authorize user actions. I found something like this:

set session "myapp.user" = '12345';
...
SELECT current_setting('myapp.user');

and it seems to work - I thought that "myapp.user" should be declared in .conf file but it seems that I can create session variables on the fly (I didn't change .conf file at all).

Is there any disadvantage of doing like this?

Before version 9.2, you needed to add your custom class variable to custom_variable_classes parameter in postgresql.conf, like:

custom_variable_classes = 'myapp'

In 9.2, this requirement has been removed:

  Remove the custom_variable_classes parameter (Tom Lane)

  The checking provided by this setting was dubious. Now any setting can
  be prefixed by any class name.

So, since 9.2 you can just set your custom class variable as you are doing currently, no need to worry about changing postgresql.conf.


MariaDB User Define Variable throws warning if it is empty I have 2 queries: the first query result will be passed to the second query using a user-defined variable. (This implementation is because of a performance issue with the table after applying proper indexing on large scale database)

The queries look like this:

SET @some_var='';
SELECT sids INTO @some_var
  FROM fsettings
 WHERE cid=1
   AND tid=9
   AND tp=1
   AND IsDelete=0;

SELECT name
  FROM `tlivewsts`
 WHERE id IN( @some_var )
 ORDER BY FIELD( id, @some_var );

I get a result within 1-2 sec. (before it was 30 sec.)

However, my issue is what happens if the first query returns no result (empty variable). Then, the second query throws awarning in mysql.

My question is: Is there any possible way to check whether the user-defined variable contains no result, and then directly return "0 results found"? I know this can be done through function/sp in MariaDB; I want to know if that is possible using a simple query?

I have gone through MySQL user-defined variable in WHERE clause for reference, without finding a solution.

After bit of R&D found below answer AND FIND_IN_SET works fine.

SET @some_var='';
SET @some_var:=(SELECT sids
  FROM fsettings
 WHERE cid=1
   AND tid=9
   AND tp=1
   AND IsDelete=0);

SELECT name
  FROM `tlivewsts`
 WHERE FIND_IN_SET(id ,@some_var )
 ORDER BY FIND_IN_SET(id ,@some_var );

I hope this solution is useful to some needy like me :)


Oracle regexp_like square brackets inside character set This questions is in regards to Oracle, pl/sql, and the regexp_like function.

I am trying to build a character set that will match on all typical special characters. My character set currently looks like:

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

I would like to add the square brackets to this character set, however, whatever I try to add ']' is not working. Here is a simple example that illustrates the problem:

select
  case when regexp_like('w]ord', '[\]]+') then 'true'
  else 'false' end
from dual;

This returns false, meaning it did not match the ']' character. Curiously, I can get the '[' character to match because this returns true:

select
  case when regexp_like('w[ord', '[\[]+') then 'true'
  else 'false' end
from dual;

Why don`t  you dip into the manual  SQL Language Reference, Appendix D, Oracle Regular Expression Support:

[]
Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A non-matching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.
To specify a right bracket (]) in the bracket expression, place it first in the list (after the initial circumflex (^), if any).
To specify a hyphen in the bracket expression, place it first in the list (after the initial circumflex (^), if any), last in the list, or as an ending range point in a range expression.


How to alter tables safely to set row_format as Compressed or Dynamic in Mariadb Galera Cluster? We have some tables with row_format=Compact. The biggest table is around 30Gb, so how safely i can alter tables and set the row format as Compressed for the big table and Dynamic for other three tables which are under 10GB each? It is a cluster with three nodes with mariadb v10.4.12 and Galera 4. Running DDL / alter tables and change the row format can leads to cluster blocking and maybe it took long time!

Can anyone help and guide me how i can do it? Do you think using RSU mode helps here and is it safe since we talking about changing the row format?

Since you are not really changing any data nor the schema, just the underlying row_format, seems like RSU would be better, and a lot less invasive.

You might also take each node out of the cluster while doing the ALTER, or at least remove the one node from the proxy's list.  This should avoid letting queries hit the table.

I feel pretty sure that the table will be blocked from writes and possibly reads during the conversion.

It would be wise to test this on an offline cluster, including testing reads/writes.


How can I get my linked server working using Windows authentication? I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL Server on each of the servers in order to enable Kerberos. I've done that and both now show the authentication scheme to be Kerberos, however, I'm still facing the error:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".

In Active Directory, I can see that the service account for ServerB is trusted for delegation to MSSQLSvc, but I noticed that the service account for ServerA does not yet have "trust this user for delegation" enabled. Does the target server also need to have that option enabled? Is anything else necessary to be able to use the current Windows login to use a linked server?

Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop.  So, yes the server needs to trust the user for delegation.

The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem.

Your Windows Account must have access to both ServerA and ServerB.
You must not have the setting "Account is sensitive and cannot be delegated."
Both ServerA and ServerB must have their own SPN registered.
The servers must be TCP/IP or named pipes connected.

The SQL Server Books Online article that offers some more detail is "Configuring Linked Servers for Delegation":
http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx


How to avoid large joins when determining which rows a user is authorized for? Typical wep app situation: you have logged in users querying for resources exposed through an API. As a hypothetical example, company administrators quering for their employees' time sheets:

company <- employees <- time_sheets
company <- admin

Say I wanted to get all the time sheets that I'm authorized for/I have access to as an admin of that company.

Without any optimizations, in the above I'd have to join admin with company with employees with time_sheets to determine all the time_sheets I can look at. You can see how for a wider schema the joins could add up very fast. I realize that in modern databases joins are ludicrously fast, but I'm guessing this is still a case you don't want to completely ignore.

Is there a good solution to this? One could potentially have a separate admin to time_sheets many to many join table just to speed-up those queries, but then maintaining it might be tricky.

Any suggestions?

Like most things it's all about trade-offs. You can denormalize, maintain materialized views, etc, but all that has its own costs. It really depends on workload, query patterns, response time priorities, maintenance burden you're willing to bear, etc.

In general I'd recommend starting with the properly normalized design and join chain, but doing it via views for common queries. This allows you to later replace the view with a trigger-maintained materialized view, to selectively denormalize the schema, etc, without messing with the app's queries.

Don't try to fix it until you know it's actually a problem.


stored procedure times out while subsequent runs take 1/6 the time So I have this stored procedure which is running very slow (and timing out) in one particular production environment.  This stored procedure produces an xml of profile data.  In all other environments, the sp runs very fast.  After tracing the procedure in the particular environment, I found that the final "SELECT" statement was taking up 96% of the total time (should be under 30%).  This statement involves multiple SELECTS, UNIONS, and JOINS.  I will refer to this as the "Last Select" statement.

On subsequent runs, the "Last Select" statement, and subsequently the whole batch,  took much less time.  What is especially curious is that subsequent runs used many more profiles (15,000 new in the second run, 25,000 new in the third run).

Comparing the query plans, I found some interesting differences between 10000.trc and 25000.trc.  Both of the following were seen in steps before the "Last Select"

The faster run (25K) utilizes many operations involving parallelism.  I do not see any operations involving parallelism in the slow run.
The faster run uses “hash matches” and “merge joins” for inner and outer Joins (instead of nested loops).  With a few exceptions, the slow run seems to always use nested loops.

The slow run uses many clustered index scans and clustered index seeks during the final select that the fast run does not.  The slow run also uses a "concatenation" step (not found in the fast run) which appends multiple tables to make an output table.

Here is a summary of the times;

Any help is appreciated.  Thank you!

Sounds like stats are out of state, and SQL is going a full scan and loading all the data into buffer pool on the first run, then accessing that data from buffer pool for the rest of the runs.

Try flushing the buffer pool between runs and see if the times increase to confirm this.

Also can you post the execution plans?


Good tool for making MySQL queries graphically? After working a while with Access 2010 I have fell in love with the way you make queries. You just pick the tables you want in your SELECT query, then you get all the JOINs laid out automatically, and you can just pick and choose what you want.

However, I cannot find any good similar tools for MySQL. I tried MySQL Workbench once, but it wasn't very easy to use compared to Access. I'm seriously considering making a parallel Access database to construct the JOINs, even though it requires me to do structural changes both in Access and phpMyAdmin, and to make dummy data in Access, and to convert the SQL to MySQL-friendly code..

Are there any GOOD tools out there to help me do this instead of my Access/MySQL in parallel approach?

You can try Visio. Here's a related SO post.

Here's a how-to about making the connection.


What are the minimal permissions required for the "Users and Privileges" tab in MySQL Workbench to function in MySQL 5.6 My ultimate goal is to allow administration for the purposes of password resets with minimal possible permissions given.  It seems to me that with the UserAdmin role, or even UserAdmin with SecurityAdmin, the "Users and Privileges part of MySQL Workbench should work; but it doesn't.  Does anyone know what custom role privileges and/or GRANT permissions are required to allow the use of Users and Privileges?

UPDATE:
It seems the minimum I need to get it to "work" is CREATE USER AND  SELECT; but that doesn't help me do what I want to do, which is be able to assign some privileges (SELECT/EXECUTE) on different schemas.  This does not seem to work as intended because even when I have the GRANT OPTION, RELOAD, or even SUPER and/or SHOW DATABASES in addition to SELECT and EXECUTE, I still get an error when trying to add or delete SELECT privileges on a specific schema that says: `(u'Error revoking privileges for @ in schema , 'You must have the GRANT OPTION privilege, and you must have the privileges that  you are reovking').

Consider I have followed what the error implies (unless I am missing something), it should work, but it doesn't.

So to clarify my question: I would like to know the least amount of administration privileges required for an administrative user to be able to:
1. create a user and/or modify passwords
2. assign SELECT and EXECUTE privileges to that user on specific schemas (without Workbench throwing errors that aren't necessarily true -- as even with the error, it creates the privileges, but it won't remove them [i.e. I can't delete it after I make it under that user]).

UPDATE2:
As far as I can tell, after much trial and error and process of elimination: the titles under "Administrative Roles" with associated descriptions, such as UserAdmin and SecurityAdmin, do not work as their descriptions describe.  The only thing that accomplishes the above is to grant DBManager, which is what I was trying to avoid.

A partial answer: for just resetting Passwords, I was able to get it to function with CREATE USER, RELOAD, SELECT, and UPDATE.  For actually granting schema privileges to the user, it continues to give an error up to DBA.  For example, it successfully grants the privilege, but then throws the error about revoking (as mentioned above), then if you close the "Users and Privileges" window and reopen it, you can see the schema grant actually applied and saved, but you can't delete it (same revoke error and it doesn't stick like when adding).  I still don't understand what privilege is driving the error; much trial and error and process of elimination was done.


Swap utilization is high on Oracle server How do I reduce swap utilisation on a Linux server which is running as an Oracle 11gR2  Server?

Currently it shows above 80% in Enterprise Manager.

How do I identify which processes use swap memory in Oracle and how do I reduce it?

Oracle database is a database server and not an operating system, it does not know about virtual memory management and swap usage.
First identify the processes using swap to confirm that it is related to the database:

https://stackoverflow.com/questions/479953/how-to-find-out-which-processes-are-using-swap-space-in-linux

Some common pitfalls related to memory management and swapping when using Oracle database on Linux:

Even senior DBAs often treat pga_aggregate_target as a limit, but it is not a limit, it is a target value. PGA can grow way behind that value, and the value of this parameter should not be used when calculating memory requirements, because it may result in undersized memory amount. You can check the maximum PGA size since instance startup from V$PGASTAT. You may see the maximum PGA allocated to be multiple times of pga_aggregate_target. This requires further investigation in the database.
The value of filesystemio_options parameter is by default none. This means the database does not use direct I/O and because of that I/O operations are cached in the filesystem cache (when using a regular filesystem and not ASM). This coupled with a default high value of vm.swappiness often resulted unnecessarily high memory usage and swapping. filesystemio_options should be set to setall, and it is recommended to decrease vm.swappinness from the default 60/30 value (RHEL6/RHEL7).
It is common knowledge that Oracle databases allocate memory for PGA and SGA. Still most people overlook the fact that database server processes also allocate some memory that is not part of the SGA (obviously, it is not shared memory) and also not part of the PGA (required for e.g. online patching). If you have several hundred or thousands of database processes, these small per process memory allocations may add up to a significant amount of memory. Queuing requests and connection pooling should be implemented in the application server layer, not in the database layer. The number of database processes should be kept relatively low.
Linux keeps track of shared memory usage for each database process seperately. With a large number of processes and a large SGA, this accounting information may take even more memory than the PGA+SGA together. You can check the amount of memory used for this with grep PageTables /proc/meminfo. If you see a significant amount of memory here (like several GBs), that memory is kind of wasted memory, and you should configure your operating system and database to use HugePages. With HugePages configured, typically the database uses 2 MB memory pages instead of 4 KB ones and this 500-fold increase in page size results in 500 times less memory required for accounting shared memory.


Changing system time on MySQL server I've discovered that my MySQL server is 10 minutes behind the current time because NTP was not running for some reason.

It is a single node and running MySQL version 5.6.15-56.

I know that changing time can affect replication in clusters but does it have any effect on single node servers?

Maybe none of these matter, but here are some guesses:

Strange values in the data (in your application) due to 10 minute jump.
Out of order things if you need to back up 10 minutes.  Search your code for ORDER BY.  (Other constructs could be affected, too.)
cron jobs and MySQL Events that fail to run or run twice.  (If you have trouble here, re-code them to protect themselves.)

There are probably more things, but I can't think of any 'dangerous' ones.


Get Master Server Information from Slave instance We need to get Master server information such as server IP and Port, we know this information is dumped into master.info file by default but what if it required within MySQL prompt as we are using windows enviorment and can not use system command too to read file from bash being in MySQL prompt and couldn't restart instance as well.

Is there any way of getting Master Server info from Slave server using some sort of dynamic select statements within MySQL prompt.

MySQL Version is 5.6.26 community  and  OS is Windows 2008R2

Any help is much appreciated.

Look in the mysql database.  You will see that some things that used to be files are now tables.  I think you are looking for slave_master_info.  It can be treated like a table for doing SELECTs.


view corrupted when viewed using design feature All,
I had a very strange thing happen last Friday. We have a table that is updated on a daily basis using a job that fires a proc that utilizes a view. The columns were being transposed from the view to the table being updated i.e. column A from the view was updating column D in the table instead of column A as it should have.

When I right clicked the view and selected "Design" from the menu I could see the problem was indeed that the sql statement generated was being written to update column D in the table with information from column A in the view.

I suspected a change to the view definition so I right clicked the view and scripted it out to see when it was last updated and when I did this the script generated was correct and completely different from the sql generated when I chose the "design" option. At this point I dropped the view and recreated it from the script. Now all is well.

Has anybody ever seen this? I've been working with SQL server since 2000 and have never come across this. We are using SQL2005 SP4 on Win2K3 (upgrading to 12 soon). Any insight would be appreciated.
-Paul

Yes, the view designer is a horribly crippled, buggy piece of garbage you shouldn't be using to "design" your views. It should be called the view destroyer, because it can do exactly what you've observed: re-write any non-standard T-SQL into what it thinks you meant. And by "any non-standard T-SQL" I mean anything not predicted by the two developers who likely spent about a day testing this "feature."

Always, always, always modify your views using Script View As > Alter to > New Query Editor Window.

See these related Connect items:

http://connect.microsoft.com/SQLServer/feedback/details/465498/ssms-designer-is-sending-incorrect-alter-statement-to-engine-when-we-have-geomerty-data-type

Where I mention in the workarounds:

  Obviously a better approach is to right-click the view, script as alter to new window. I wonder when they'll admit defeat and ditch the visual designers altogether. They cause more problems than they solve, IMHO.

http://connect.microsoft.com/SQLServer/feedback/details/362679/ssms-right-click-on-a-view-to-edit

Where I do something similar, and

http://connect.microsoft.com/SQLServer/feedback/details/459202/no-edit-option-when-right-clicking-on-a-view-in-management-studio-2008

Where I ask:

  Didn't Edit bring up the horrible graphic designer for views, which was riddled with bugs and lacked support for several basic T-SQL constructs? Isn't that what Design does now?


How does using separate schemas affect SQL Server 2008 performance? I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or stored procedure, and the prefixes mean we have to scan five or six xharacters in before we even see the start of the unique name. I would like to use separate schemas for tables that are just used to drive the UI (menus, roles by person, etc.) and for those that are dimension tables vs. fact tables, etc.

My question is, will there be performance impacts from using multiple schemas (schemae?) was opposed to the good old dbo for everything?

There may be an impact on the performance of the query optimizer, albeit a small one, depending on your coding style. If you reference a table without a schema, the optimiser has to try to identify the table first by checking tables within the users default schema (if there is one), then using dbo., and then everything else. If you explicitly reference the tables as schema.table, which is probably good practice anyway, then even this minor overhead will be avoided.


oracle tablespace extending While under massive write (insert and create table as) load, when Oracle RAC needs to extend tablespace, all write activity gets blocked for however long it takes it to finish extending (tens of minutes), with all sessions blocked by "data file init write".  Is there way to configure Oracle to extend the file proactively so that things can keep moving while it is doing it?

30min for 1TB is quite normal. That all writing stops is also normal if your tablespace completely run out of space. When writes has nowhere to go they have to wait for the RESIZE operation to complete. If one extends datafile while there is still space in it database I/O does not stop.

Just why you extended for such huge amount? Now those +1TB will add to your RMAN backup. Of course they will compress well but still not 100% and RMAN will need time to read all those blocks.

I would setup autoextend on those tablespaces:

ALTER DATABASE DATAFILE '+DG/sid/datafile/datafile_01.dbf' AUTOEXTEND ON NEXT 10G MAXSIZE 30G

Then you would not get wasted space in tablespace and also even in case your data grows fast and catches up with autoextend operation - there will be only small delay in writes because 10GB will not take a long time to format.

And Oracle will autoextend tablespace proactively. It will not wait for the last blocks to fill up.


how to extract current year and combine with specific month and date as a date I have to get the current year with June the 16th as a date.

For example, if I run the query now I want to get 2015-06-16.

I tried the following:

CONVERT(DATETIME,cast( (cast(YEAR(GetDate()) AS VARCHAR(4))+'-06-16 00:00:00.000' as varchar (24) ),102)

but it didn't work.

The following T-SQL gives you the result you need.

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GetDate()))+'-06-16')


Potential schema for product failures and repairs I'm creating a schema for product failures and repairs and would appreciate feedback on the schema.

The database is intended to capture all product failures and corresponding repairs. A product can fail multiple times and can be repaired multiple times(many to many relationship). Not all products fail, and no data will be captured for products that don't fail.

All products have a job which dictates the product_pn(product part number), the due date, and the qty to ship. A product is assigned a unique DMR number if it fails. DMR means the product is discrepant material and should be quarantined until a repair is made to determine if the product can be shipped. A given product will never have more than one DMR number. The DMR number is used to uniquely identify a given product to know what failures and corresponding repairs have occurred(a person can enter the dmr number to see the full history of failures and repairs for a given product). If a product is successfully repaired the product is assigned a unique sn(serial number). The DMR number is linked to the sn and removed from the product, so a history of all failures and repairs can be maintained for any product. Some products will be scrapped and will never receive a SN.

A product failure is reported by a technician(any technician can report a failure), and requires a description of the failure and what operation the product failed at(inspection, programming, testing) as well as a timestamp of when the failure was reported.

A repair requires a disposition(repaired, use-as-is, or scrap), a repair code(process not followed, bad test equipment...), a description of the repair, and who the qualified technician is that repaired the board. We also need to capture any components that were replaced on the product(many components could be replaced on a product), and a timestamp the repair information was submitted.

Here is what I have for the schema so far:

I'm a bit unsure when to use an enum vs creating another table(e.g. disposition can be scrap, repaired, or use-as-is). Am I on the right track?

Also, I'm planning on using MySQL innodb tables, any problems / suggestions with this? This is for a small ajax based web app... Thanks in advance.

Edit: Thanks for all your help so far. Sorry I'm new here so had to try to clarify the original post based on feedback.

Model

Here's a possible model. After I drew it I noticed that the difference with yours is that the product part number is  now in the Product table; I think it should be this way as it's a characteristic of the product. Then the Job refers to a Product via its part number, and not viceversa.

Each table uses a surrogate primary key for clarity. This is not really necessary in several cases e.g. in RepairComponent, so feel free to replace it with a composite primary key.

The Product table stores a product. The field Product.pn (part number) should identify your product since it is manufactured; the product is then assigned a DMR when it fails and then, if it is repaired, a S/N.

Product
------------------
pn (PK)
DMR (IDX unique)
SN

Job table:

Job
------------------
id (PK)
Product.pn (FK)
due_date
quantity_to_ship

  A product can fail multiple times and can be repaired multiple times (many to many relationship).

A product may fail multiple times but each repair concerns only one product, so it's a one-to-many relationship. You can hence have simply one table Repair (that stores a repair event) referring to Product.

Failure
------------------
id (PK)
Product.DMR (FK)
description
operation
date
Technician.id (FK)

Repair
------------------
id (PK)
Product.DMR (FK)
disposition
code
date
Technician.id (FK)

RepairComponent
------------------
id (PK)
component_pn
Repair.id (FK)

Technician
------------------
id (PK)
name

Enum vs table

  I'm a bit unsure when to use an enum vs creating another table (e.g. disposition can be scrap, repaired, or use-as-is). Am I on the right track?

If you are dealing with an immutable set of operations (i.e. you're absolutely positive that Repair.disposition will always be one and only one of the options you listed) then it's ok to use an enum. Otherwise, your ER model will be much more flexible if you create another table:

DispositionTypes
------------------
id (PK)
description

as now you can create all kinds of new disposition types e.g. "Repaired", "Use as is", "Scrap", "Sell to China", or "Bury in landfill". In this case, you remove Repair.disposition and replace it with a FK DispositionTypes.id instead.

The advantage of this approach is that you can set up authorization levels for Technicians to perform a specific disposal (it is not mentioned in your question but it shows in your model, if I'm not wrong). This is done via a join table Authorizations:

Authorization
------------------
Technician.id      (PK) (FK)
DispositionType.id (PK) (FK)

Same talk for RepairCode:

RepairCode
------------------
id (PK)
description

Personally I like to design databases that allow for flexibility, but the choice here it's up to you.

Choice of the DB engine

A MySQL database with InnoDB (which is the standard engine in MySQL) will work fine, so it's a good choice.


mySql Relationships / Index - Checking for duplicates I have this setup on innoDB:

TblUser
user details

TblConnection
User Connection

TblConnection also has these indexes: User, Connection where User is the foreign key of TblUser.id and another index: Connection, User where Connection is also a foregin key to TblUser.id

Now I want to make sure that when adding a new row I have complete uniqueness between column combinations. By this I mean I want that 1,2 is treated the same as 2,1 and therefore an INSERT is not made.

I'm not sure if I understand correctly. As long as you have a unique key on both columns, it does not matter what order the columns (or combination) they are in. Otherwise, the tuple must also exist in the referencing table in order to enforce it's uniqueness.

CREATE TABLE tbluser (
  user int,
  details int,
  primary key(user, details));

CREATE TABLE tblconnections (
  user int,
  details int,
  connection int,
  primary key(user, details, connection),
  unique key(user, details),
  foreign key (user, details)
    references tbluser (user, details));

Make sure that you specify a column list when you make this INSERT.


Keeping iterator in consistent state I was reading this post to understand how things are done in Sql Server:
http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/

If I understand it correctly, data is being sent to client as is being needed. Before we fetch next batch of data to the client, execution of query is suspended. If we do range scan then metadata about last returned row will be stored somewhere.
What happens if in the meantime there is a page split and last returned row is on the new page?
Is this possible? Does this operator/iterator (clustered index scan for example) holds latch on page whole time?
This would be extremely inefficient, so Sql Server must have some other way to keep it in sync?

My question is - what metadata is stored and how does Sql Server keep it in sync?

No SQL Server does not hold a latch. The storage engine keeps track of the current index scan position using a "cookie". The cookie is revalidated if another process takes a latch (of a type that means the page might have been changed) on the same page since the cookie was acquired.

If the cookie (scan position) is no longer valid, the b-tree structure is traversed (using key + uniquifier information stored in the cookie) to reacquire the correct position, and the cookie updated.

In case it is of interest, here is a stack capture when the saved position is validated:

And another one just after cookie validation has failed due to a concurrent incompatible latch. The engine is repositioning the scan position using a seek to find the correct new position:

There are performance counters related to cookie activity, for example Scan Point Revalidations/sec and Used page/leaf page cookie.

There is no public documentation that describes exactly how this all works. The details above are based on observations, subsequently confirmed in conversations with Paul Randal and Bob Ward.


Keeping (Partial) Backups Small when using SQL Server FILESTREAM I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the data is changed every couple of days, so differential backups wouldn't really help keep the size down.

I had the backups working the way I needed by setting Recovery Mode to Full, creating a separate FILEGROUP for the FILESTREAM, then taking backups of only the "Primary" FILEGROUP. The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data.

SIMPLE Recovery Mode takes away my ability to do backups of specific FILEGROUPs, so I don't think that will be an option either.

My thoughts are to just move the FILESTREAM data to a separate database, but now I'm losing referential integrity and surely inheriting a host of other issues as well.

Is there any way to create partial backups in Simple recovery mode (without setting the FILESTREAM table to read only)? If not, are there any other sane solutions to my problem?

The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data.

I'm not sure if you mean the log file itself is too large or that the log file backups become too large.

If it's the former then how often were you backing the log up? Depending on the application design you may be able to reduce the size by backing up more frequently (and every 5 minutes is not too frequently). However if you were already doing that and it was still ballooning then you're probably out of luck. Why is the large log file a problem again?

If it's the latter - it sounded like you were happy to continue with simple recovery model and no point in time restores if it let you have smaller backups; in which case stay in full mode and discard your log backups.


Oracle start database with incorrect password I have a question regarding starting oracle database with in correct sys password.

I have provided incorrect password for sys user and I am still able to start database and execute statements. So wondering whether I have missed something while installation or this is how it is?

Any insight into this is highly appreciable, Below is the result of start database.

sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 15 16:50:39 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: sys/d as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             285215104 bytes
Database Buffers          130023424 bytes
Redo Buffers                6094848 bytes
Database mounted.

When your OS user is from a DBA group, you can connect AS SYSDBA with OS authentication:

  Two special operating system groups control database administrator
  connections when using operating system authentication. These groups
  are generically referred to as OSDBA and OSOPER. The groups are
  created and assigned specific names as part of the database
  installation process. [..]

  Membership in the OSDBA or OSOPER group affects your connection to the
  database in the following ways:

  If you are a member of the OSDBA group and you specify AS SYSDBA when
  you connect to the database, then you connect to the database with the
  SYSDBA system privilege.
  If you are a member of the OSOPER group and you specify AS SYSOPER
  when you connect to the database, then you connect to the database
  with the SYSOPER system privilege.

You can then use CONNECT / AS SYSDBA or even CONNECT FOO/ANYTHING AS SYSDBA, the DB will ignore the user/password.


SQL Server hitting System.OutOfMemoryException but server has 10GB free SQL Server 2012 11.0.5058.0

I'm running a very large batch of queries (about 2700) to simulate a user doing X, Y and Z in our app. I have SQL Server's max memory set to 2147483647 MB. However when I run the query I get several messages like so:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

When I look at the server it has 20GB of memory free and SQL Server is only using 4.8GB (and that number is stable).

I've tried putting some GO statements in to no avail. I don't know where it is hitting the memory issues. None of the queries return more than a few rows.

How can I get around this memory issue?

That exception usually doesn't refer to the SQL Server itself running out of memory, but instead the client workstation that is processing the results running out of memory.

You will generally see this if you are outputting a very large result set into the Output window.

To troubleshoot the issue (and possibly resolve it) allow SQL to write the results to a file rather than trying to put it all in the output window.


Grant permissions to a user to grant select to specific tables in several schemas in Oracle I administer a small datawarehouse in Oracle.
My user dwhmanager was given the SELECT ANY TABLE privilege:

GRANT SELECT ANY TABLE TO dwhmanager WITH ADMIN OPTION;

The idea was to have the ability to grant access to tables in several schemas to the developers in the datawarehouse.

Example:

GRANT SELECT ON DWHFINANCE.SALES TO johndeveloper;

However, I found my self getting the infamous ORA-01031: insufficient privileges

I can however, give "johndeveloper" the SELECT ANY TABLE privilege as well, and from that, he could select on any table on the datawarehouse, however, that's too much, not my intend.

So, what permission to I need to ask to the main DBA to grant me in order to have the power to give permissions in individual tables in other schemas?

You probably want GRANT ANY OBJECT PRIVILEGE

AS DBA:

GRANT GRANT ANY OBJECT PRIVILEGE TO dwhmanager;

AS dwhmanager:

grant select on DWHFINANCE.SALES TO johndeveloper;

Presto!


How to query for address "predictions" while applying constraints Let's say I have this simplified database scheme.

The scheme consists of three tables, street, city and zipcode that all have a many-to-many relationship to each other.

What I want to do is a prediction that queries the database for possible matches as well as predicting missing data. To put it more into an example:
Let's say the user starts typing in a city, perhaps "Wash". He already had a street typed in, for sake of me not knowing any streets, let's call it "New Street Avenue". This is a constraint, meaning it must be fulfilled in our prediction. Now I want the city to query for possible finishers of "Wash" as well as supplying missing data, in this case, zipcodes, that have a street called "New Street Avenue"

Here's how the naive approach would be:

Filter street for all keys whose name match "New Street Avenue"
Filter street_city for all the street keys and return the city keys
Inner join the city keys on city_zipcode, and then Inner join those onto zipcode

The issue with this approach: The knowledge between street and zipcode is completely ignored. That means we could end up with zipcodes and city pairs, where we know the city contains the street we seek for, but the zipcode doesn't necessarily. This would be an invalid prediction to return, because the address doesn't exist!

That means I need to "persist" the information about street further down the line.

My approach would be this:

Filter down street for the keys that fulfil our condition.
Inner Join street onto city for the keys from #1. Keep both key columns.
Inner join onto city_zipcode keeping the zipcode keys as well
Filter out all the street and zipcode pairs that are not present in street_zipcode

(On a sidenote: to "hopefully" improve performance, after each "prediction" I would put a LIMIT 10 (or similar) command since we only need a sensible amount of predictions).

This approach should work. It might not be the best optimized, but it would return the correct results. However this not only feels very dirty to me, but also opens up another problem:

I'm pretty sure it scales horribly, in table size as well as in additional tables

There is a good likelyhood there will be another table that has Many-to-Many relationships towards, for the sake of argument, street and zipcode. Now if I wanted to search for a city with a street constraint, I need to do what I did above, and add even more to also filter out any relationships between street and the new table.

I feel like I'm going into a dead end with this approach. I'd love someone to help me out here on how to approach this problem better.

3 tables is what I call "over-normalizing" -- it leads to performance problems.

"Normalizing" is used for two purposes, neither of which really apply to your use case.

Isolate something, say "city" so that it can be easily changed.  In real life, that rarely happens for cities.  ("Bombay" -> "Mumbai" (India), "Hot Springs" -> "Truth or Consequences" (New Mexico))
Save space.  Even if you have all 3 million cities on earth, spelling them out the country each time is not much burden of space.  (For countries, I recommend the standard 2-letter country-codes and use CHARACTER SET ascii.  Optionally, have a table mapping them to spelled out names.)

Zip codes:  In the US, there are about 42K zip-codes.  To normalize you could use a 2-byte SMALLINT UNSIGNED.  But the zipcode, itself, can be stored in a 3-byte MEDIUMINT(5) UNSIGNED ZEROFILL.  Zipcodes do change.  But that usually involves forcing half the users of one zipcode to adopt a 'new' zipcode.  You would have to go through all users of that zipcode (which could be INDEXed) to figure out which ones to change.

Similarly for the split-up of Yugoslavia and Czechoslovakia.  On the other hand, changing "The Republic of Upper Volta" to "Burkina Faso" is easy if you have 'normalized' it.

Plan A (millions of locations):  Simply spell out the location of each item.

Plan B (billions of locations):  Split in two:  Street address and zip+city+country

Think about it another way:  What will you do with the address?

Only for sending mail?  Then why split up the pieces; simply have some text that can be readily printed as-is.
Also used for statistics?  Then you need, say, the "country" (or "country_code") so you can do SELECT SUM... GROUP BY country_code.  This implies that country_code is a separate column.


How to create comment on table from query result I want to use a query result as comment on the table. I have tried in psql:

\set list select count(*) from department.master_data
COMMENT ON TABLE department.master_data IS :list;

But I got this error:

it=> \set list select count(*) from department.master_data
it=> COMMENT ON TABLE department.master_data IS :list;
ERROR:  syntax error at or near "selectcount"
LINE 1: ...ABLE department.master_data IS selectcoun...

While I am not sure what you want to achieve (why it makes sense to store a count as a comment, especially if your table will get new rows (or lose some to DELETEs)), but here is how what your problem is and how you can solve it.

First, check what psql thinks you have in the variable:

\echo :list
selectcount(*)fromdepartment.master_data

Clearly not the result of your query.  To store that, you'll have to use \gset:

> SELECT count(*) AS master_data_count FROM department.master_data;
 master_data_count
───────────────────
        1234567890
(1 row)

> \gset
> \echo :master_data_count
1234567890

If you would try to set the table description now, you'd get another error:

ERROR:  syntax error at or near "1234567890"
LINE 1: ...ABLE department.master_data IS selectcoun...

This is because the comment must be a (quote-delimited) text.  To get one out of your variable, you have two options.  One is adding escaped quotes to the variable using psql (using the same feature you misused setting :list:

> \set master_data_count '\'' :master_data_count '\''
> \echo :master_data_count
'1234567890'

Or you can add the quote already in your query, so SELECT count(*) turns into

SELECT $$'$$ || count(*) || $$'$$

(I used dollar quoting above to avoid typing things like ''''.)

The result you can already use as a comment, as it contains the necessary quotes.


Detect bottleneck of server via psycopg2 I have PostgreSQL superuser called "admin" which can connect to a PostgreSQL database.

I am not managing the database server. I have no root-access to this machine.

Sometimes the performance is not good.

Up to now I have no numbers, but I would like to have some.

Is there a (common) way to do small benchmarks on the production db to see how it performs?

I have something like this on my mind: Every ten  minutes some magic sql commands get execute which give me some numbers. I would like to see CPU/MEM/IO usage/performance.

The same queries lead to different performance. I guess the hypervisor hosting the PostgreSQL VM is overloaded from time to time

The current situation is not nice for me, since the customer sees the slow application, and blames me. But it is not me, it is the slow DB.

How can I proof that the DB is slow, and not the application?

(I guess this situation is common. How to do handle this?)

If you have access to PostgreSQL superuser account, then you can run arbitrary OS commands on the server as the OS user who is running the postgres server.  For example:

create temp table top (x text);
copy top from program 'top -b -n1';
select x from top limit 25;

However, if you were intentionally not given direct shell access to this server, whoever is running the server could consider this a hostile action.  It would probably be better to ask whoever is in charge to set up some OS-level monitoring solution, or explain to you any existing ones.


Is pre-sorting valuable when using table value constructor to insert multiple values into a table with clustered index? This is related to this question: Efficient INSERT INTO a Table With Clustered Index

From that question it sounds like SQL Server will sometimes pre-sort the data when inserting multiple values into a table with a clustered index to reduce fragmentation. Will SQL Server also do this when I use a Table Value Constructor to insert multiple values in an insert statement?

For example, if I have a table event_log that is clustered on a timestamp column and I insert data like this:

INSERT INTO event_log (timestamp, data)
VALUES
('03:00:02', 'data string...'),
('03:00:01', 'data string...'),
('03:00:00', 'data string...')

Will SQL Server pre-sort for me to ensure the data goes in efficiently without fragmentation or should I sort the data in my app before generating the SQL?


Issues with GRANTing permissions to AD group I have couple of Active directory groups authenticated to access my RDS SQL Server instance. I have granted access to a AD group to perform ALTER ANY connection with GRANT.

When I log in as a user of that AD group and try to GRANT ALTER any connection the SQL stmt fails : GRANTOR does not have GRANT permission

But when I exclusively create a SQL login for the user in the group and give permission to that user and try the GRANT it works.

Is it supposed to work like that? If so, then what's the point of having a group? I'm not able to find any info on this. If you know any Microsoft document that explains this behavior it would be great.

Similar issue : AD User does not inherit the permission from AD Group - for a LOGIN

You need to use AS principal.

Ref:
https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql

WITH GRANT OPTION

The GRANT … WITH GRANT OPTION specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts. When the principal that receives the permission is a role or a Windows group, the AS clause must be used when the object permission needs to be further granted to users who are not members of the group or role. Because only a user, rather than a group or role, can execute a GRANT statement, a specific member of the group or role must use the AS clause to explicitly invoke the role or group membership when granting the permission. The following example shows how the WITH GRANT OPTION is used when granted to a role or Windows group.


Change SQL Server settings without restarting services I can change SQL Server settings using Management Studio or programmatically as described e.g. here.

I would like to make this changes WITHOUT the need to restart SQL Server services. Is there any trick to do so??

It depends on what settings you changed based on http://msdn.microsoft.com/en-GB/library/ms189631.aspx. Some require a restart (RR), other can be done via RECONFIGURE

There is no trick or workaround. At all.

Edit, now we know it is Authentication mode...

This is automatically deals with the instance name

 EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'LoginMode',
    REG_DWORD,
    1; -- or 2 for mixed

Restart is still needed


SQL 2 Columns of Different Types in Same Table - Working With 'Uknown' Amount I'm very new to SQL and I've been searching for an answer for a while now, can't seem to find it.

I have 1 table of CustOrders which has 3 columns.

Table CustOrders

Name (varchar)
Number_Orders (int)
order_date (datetime)

Question

I would like to get a list of customers that have had less orders than a specific customer 'x'. How would I go about doing that?

Select Statement

I tried with this logic, which didn't seem to work and is probably all wrong, which was:

SELECT name, number_orders
FROM custOrders
WHERE number_orders < (select number_orders from custOrders where name = 'x');

It returned an empty set, which should not be empty, because I know there are customers there with less orders.

Could you please help me with this?

I am using MariaDB.

SELECT t1.*
FROM custOrders t1, custOrders t2
WHERE t1.number_orders < t2.number_orders
  AND t2.name = 'x';

PS. custOrders.name must be defined as unique by according index.


With SQL Server 2012 or newer, you can use a WINDOW function with boundaries:

SELECT *
    , SUM([Amount]) OVER(PARTITION BY [ID] ORDER BY [date_time] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM @data;

With previous version, a self JOIN can be used:

SELECT d1.ID, d1.[date_time], d1.Amount
    , SUM(d2.Amount) as running_amount
FROM @data d1
INNER JOIN @data d2 ON d1.ID = d2.ID AND d2.[date_time] <= d1.[date_time]
GROUP BY d1.ID, d1.[date_time], d1.Amount;

Output:

ID  | date_time                 | Amount    | running_amount
1   | 2015-01-01 02:01:00.000   | 9         | 9
1   | 2015-01-02 02:02:00.000   | 20        | 29
1   | 2015-01-02 02:03:00.000   | 20        | 49
2   | 2015-01-01 02:01:00.000   | 20        | 20
2   | 2015-01-01 02:02:00.000   | 20        | 40
3   | 2015-01-02 01:00:00.000   | 20        | 20
3   | 2015-01-03 01:00:00.000   | 10        | 30
3   | 2015-01-04 01:00:00.000   | 5         | 35


SSIS creates too many connections to a database I wonder if anyone had experienced anything similar to the following problem?

I have a SSIS package containing 10+ data flow tasks which I'm using to extract data from an OpenEdge 11.3.002/Progress database to SQL Server 2016 via an ODBC driver.

However, I've been informed by the dba of the progress database that I've created so many connections to the Progress db via SSIS that on several occasions they've needed to restart the Progress database to kill all the connections that SSIS has made as no-one else can connect to it anymore.

I've never experienced an issue when SSIS makes 'too many connections' to a database so I'm trying to reduce the number of connections it opens to Progress. Approaches I've tried so far:

On the connection manager to the Progress DB in SSIS I've set the Retainsameconnection property to true;
Maxconcurrentexecutables property is set to 2 (So only two data flow tasks can run concurrently);
Enginethreads property is set to 5.

But these don't appear to limit the number of connections (enough).

Are there any other ways I can reduce the number of connections SSIS makes to another ODBC database to say max 100 connections (And ensure SSIS releases these afterwards)?

Currently using:

Progress Openedge db 11.3 to extract data from
Visual Studio 2015 for SSIS packages
SQL Server 2016 to load data into

Set precedence constraints between your data flow tasks so that they execute sequentially. https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/


Fetching data from 3 tables Wanna fetch data from 3 tables by mysql query to display in feed. But stack. My query is

SELECT f.leaderId, f.followerId, c.ccId, c.ccTitle, c.ccCity, c.ccCountry, c.ccFlag, c.uId, c.ccStartingDate, c.ccCreatedAt, l.uId, l.ccId
FROM followingrelations f, conferencecreate c, likesbyuser l
WHERE f.followerId = 'login_user_id'
      AND c.uId = f.leaderId
      AND c.ccFlag = 1
      AND c.ccStartingDate >= 'current_time_and_date_now'
      OR l.uId = f.leaderId
GROUP BY c.ccTitle
ORDER BY c.ccId DESC;

Expectation:

Want to fetch data where uId and leaderId are matched from followingrelations and conferencecreate tables respectively.
Beside above condition, want to fetch data also if any user likes any post. The condition I putted like this way OR l.uId = f.leaderId

Am I on right track?

I implemented this query but nothing displaying. Any idea how to solve this issue plz?

My database design is in following:

Please try it.

SELECT f.leaderId, f.followerId, c.ccId, c.ccTitle, c.ccCity, c.ccCountry, c.ccFlag, c.uId, c.ccStartingDate, c.ccCreatedAt, l.uId, l.ccId
        FROM  conferencecreate c
left join followingrelations f on c.uId = f.leaderId
left join likesbyuser l on  (c.ccStartingDate >= 'current_time_and_date_now' or l.uId = f.leaderId )
        WHERE f.followerId = 'login_user_id'
        AND c.ccFlag = 1
        GROUP BY c.ccTitle
        ORDER BY c.ccId DESC


Split or combine tables? Background

I have a table with the below format:

id(int) type(varchar)   Name(varchar)   Description(varchar)    Value(decimal)

There are three different types possible, data from each of these types will never be compared against each other and for all intents and purposes are completely unrelated apart from the fact that they can share this table structure.

This is a very small table (10 000 rows), however it will be regularly joined with tables that have around 10 million rows based on the id.

Question

Would splitting the table into three separate tables based on the type be beneficial for performance? If so would it be a negligible difference?

Doing so requires little modification to existing code, and no additional types will ever be added.

From a design and readability point of view is it preferable to have this table together or split, considering that in other instances in the DB separate tables exist for each type as they do not share a common structure.

Splitting won't give you much performance gain. Joining this table using ID (Should have Clustered Index created for this columns) will give you a better performance.


MySQL - Master/Slave Replication with different server specs I have a Master/Slave replication (using MySql 5.1 / InnoDB).

Currently both my master & slave are running on the same type of server (32 GB RAM, 2 x Intel Xeon 5520 Quad Core 2.26 GHz (8 cores)) and have the same type of configuration (in terms of innodb_pool_size etc.)

I am considering upgrading my master to a stronger machine (48GB RAM, 2 x Intel Xeon 5640 Six Core 2.26 GHz (12 cores)).

My question is: Is it possible to upgrade just the master (increase his buffer_bool_size etc.) and leave the slave at his current setup?

Yes you can upgrade the Master's my.cnf file to handle the new environment.

I have an additional suggestion.

InnoDB prior to MySQL 5.1.38 cannot use multiple CPUs.

You can do one of two things that will not affect the Slave

Upgrade to Percona Server 5.1
MySQL 5.1.38 has InnoDB Plugin (dsiabled by default) which acceses mulitple CPUs

If you have MySQL 5.1.37 or prior, upgrade to the latest MySQL 5.1 and install InnoDB Plugin
If you have MySQL 5.1.38+, install InnoDB Plugin

Once you do, one of the above, you can configure the necessary options for accessing multiple CPUs

About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?


Mysql query not working CREATE DATABASE `menu`;

USE menu;

DROP TABLE IF EXISTS `fish`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’,
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
LOCK TABLES `fish` WRITE;

INSERT INTO `fish` VALUES
(1,’catfish’,’8.50’),(2,’catfish’,’8.50’),(3,’tuna’,’8.00’),
(4,’catfish’,’5.00’),(5,’bass’,’6.75’),(6,’haddock’,’6.50’),
(7,’salmon’,’9.50’),(8,’trout’,’6.00’),(9,’tuna’,’7.50’),
(10,’yellowfin tuna’,’12.00’),(11,’yellowfin tuna’,’13.00’),(12,’tuna’,’7.50’);

UNLOCK TABLES;

I'm currently reading this book on how to use MySQL with python and would like to know why this query from the book is not currently working.( MySQL version 5.6 and workbench 6.2 in Windows 7)
Things I have tried

Typing in the code by hand
Copying and pasting from the pdf into notepad++, workbench and notepad
I copied the file into notepad and saved it in different encoding such as UTF-9

It is probably something simple I am missing, but I am new to this.
The error message get when running the query.

11:25:50  CREATE TABLE fish ( ID int(11) NOT NULL auto_increment, NAME varchar(30) NOT NULL default ‘’, PRICE decimal(5,2) NOT NULL default ‘0.00’, PRIMARY KEY (ID) ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘’, PRICE decimal(5,2) NOT NULL default ‘0.00’, PRIMARY KEY (ID) ) E' at line 3  0.000 sec

Your problem is the quotes you are using for the default of NAME.

Here is your original create table from the question

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’, <<--- LOOK HERE
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You gave default ‘’ and the mysql client does not understand it.

You need to express it with standard single quotes like this : default ''

I just noticed that a_horse_with_no_name's comment already says this.

Don't change anything else. Leave all other quotes as is.

Give it a Try !!!


How to create a Postgres trigger to ensure field is non-updateable I'm trying to create a Postgres trigger to ensure after a column's value has been set, it cannot be updated (basically make it readonly). So far I've come up with the following draft trigger.

1) First, am I on the correct track?
2) Is there value in returning NEW?
3) Must I preface original_id with NEW.?

CREATE FUNCTION check_id_change() RETURNS TRIGGER AS $$
    BEGIN
        IF OLD.original_id <> NEW.original_id THEN
            RAISE EXCEPTION 'cannot change original_id';
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER client_update_trigger AFTER UPDATE ON client FOR EACH ROW
      EXECUTE PROCEDURE check_id_change();

Create a BEFORE UPDATE on your specific column original_id.
NOTE since the application which is using the DB should be aware of its constraints, therefore it is better to raise an exception when the column is getting updated, in order to notify the application layer of a failure, instead of just ignoring the update.

CREATE OR REPLACE FUNCTION check_id_change()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  IF NEW."original_id" IS DISTINCT FROM OLD."original_id"
  THEN
    RAISE EXCEPTION '"original_id" column cannot get updated';
  END IF;

  RETURN NEW;
END;
$BODY$ LANGUAGE PLPGSQL;

CREATE TRIGGER client_update_trigger
BEFORE UPDATE OF "original_id"
  ON "client"
FOR EACH ROW
EXECUTE PROCEDURE check_id_change();

Or using WHEN condition

CREATE OR REPLACE FUNCTION check_id_change()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  RAISE EXCEPTION '"original_id" column cannot get updated';
END;
$BODY$ LANGUAGE PLPGSQL;

CREATE TRIGGER client_update_trigger
BEFORE UPDATE OF "original_id"
  ON "client"
FOR EACH ROW
WHEN (NEW."original_id" IS DISTINCT FROM OLD."original_id")
EXECUTE PROCEDURE check_id_change();

NOTE use IS DISTINCT FROM comparison instead of <> or != unless the column has NOT NULL or you're sure that it's value will never be NULL.


Postgres authentication fails: log refers to "line 90" in pg_hba.conf that shouldn't exist Whatever settings I try, I keep getting connection errors, from external clients as well as trying to connect from a running console on the server itself.

My last attempt for a local connection with "user_test":

psql -h /var/run/postgresql -p5433 -U user_test

When I checked the log on /var/log/postgresql/postgresql-12-db_test.log he last line on the log puzzled me: why is the log referring to "line 90" in the pg_hba.conf? That line should not exist???

2019-12-23 00:24:50.620 CET [11398] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2019-12-23 00:24:50.620 CET [11398] LOG:  listening on IPv6 address "::", port 5433
2019-12-23 00:24:50.623 CET [11398] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2019-12-23 00:24:50.649 CET [11399] LOG:  database system was shut down at 2019-12-23 00:24:50 CET
2019-12-23 00:24:50.654 CET [11398] LOG:  database system is ready to accept connections
2019-12-23 00:25:22.452 CET [11426] user_test@user_test LOG:  provided user name (user_test) and authenticated user name (postgres) do not match
2019-12-23 00:25:22.452 CET [11426] user_test@user_test FATAL:  Peer authentication failed for user "user_test"
2019-12-23 00:25:22.452 CET [11426] user_test@user_test DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"

Here's a summary of my setup:

Fresh installation of postgesql 12 on ubuntu cloud-server
created a cluster "db_test" (alongside to the standard installation "main")
added a role "admin" and assigned user "user_test" to the cluster
told postgres to listen to all ports (ALTER SYSTEM SET listen_addresses='*';)
modified pg_hba.conf (sudo nano /var/lib/postgresql/12/db_test/pg_hba.conf)

That file currently reads (had many other settings none worked):

# TYPE  DATABASE USER ADDRESS METHOD
local all user_test trust
hostssl all all 31.164.122.223 md5

restarted the process using either:

postgres@servername:~$ sudo service postgresql@12-db_test restart

or

pg_ctlcluster 12 db_test start

User jjanes' comment helped me find the real hba-file. It's actually here:

/etc/postgresql/12/db_test/pg_hba.conf

(and not in the /var/lib/postgresql/12/... Don't remember why I had expected it there. As I'm new to all this, the branches of a linux directory tree are misterious to me.)


MySQL - Advice for using two tables, one each for open and closed records? I'm migrating an app from ASP/MSSQL Server 2008 to PHP/MySQL. In the previous app, I had an object using multiple joins and advanced search queries with hundreds of thousands of records. I split the records into 'table1' and 'table1_closed' so I could keep the open-records table speedy. This worked in speeding up the queries by a tremendous amount (the closed table is orders of magnitude slower), but resulted in some weird workarounds to allow closed records to be moved back to the open records table (manual locks and such). Needless to say it’s hacky and buggy.

My question is, in MySQL, does anyone see the need to keep doing it this way, or would you merge the two tables back to one and just use an indexed field to differentiate? Is MySQL more adept at advanced queries with joins on very large recordsets?

From comment:

Select  t1.*, t2.*, t3.*
    from  properties t1
    Join  inspection_groups t2  ON t1.id = t2.property_id
    Join  inspection_types t3  ON t2.inspection_type_id = t3.id
    where  (      t1.address = 'x'
              or  t1.city = 'x'
           )
      And  t2.inspection_type_id = 1
      And  t2.is_active = 1
    ORDER BY  t2.created_at DESC
    LIMIT  1,12

Add a column to distinguish open/closed.  It should be small (TINYINT or ENUM).

Then pick one:

Plan A:

Then have most indexes be composite and start with that column.  These indexes will make it efficient to find just opened (or just closed) rows.

If you also need to find both opened and closed, then also have index(es) that do not start with the new flag.

Plan B:

PARTITION by the new column.


TempDB Log Space and ACTIVE_TRANSACTION Our monitoring solution (SCOM) is currently flagging that the tempdb log is running out of space.  However we have auto grow set to 1GB chunks for the log and we have 25GB of space left on the drive.

I looked at what the log_reuse_wait_desc was and found  it to be ACTIVE_TRANSACTION

I started to wonder if for some reason the log file was filling up and the auto grow not kicking in, and after some research I found that the log file should still grow even during an ACTIVE_TRANSACTION.

I found an article on a similar issues where the tempdb log ran out of space:

http://sqltimes.wordpress.com/2014/07/05/sql-server-error-messages-the-transaction-log-for-database-tempdb-is-full-due-to-active_transaction/

Here they issued a CHECKPOINT to resolve the issue on tempdb.  I know a CHECKPOINT flushes dirty pages to disk, however I do not understand how this would fix the ACTIVE_TRANSACTION problem?

Furthermore I also do not know why we get this alert when there is plenty of space.  Is there a situation where a tempdb can fill and auto grow not work for some reason?

As Max mentioned the alert probably fired just prior to the log growing/needing to grow. SCOM collects transaction log free space % although I am not sure at what threshold the alert will fire.

here is a quick example to show you what state tempdb is probably in when you get these alerts but no log file growth.

first create a database, set recovery to full, and back it up

    create database tlogspace
    on(name=tlogspace_dat,
        filename='c:\temp\tlogspace.mdf',
        size=4MB)
    log on (name=tlogspace_log,
        filename='c:\temp\tlogspace.ldf',
        size=1MB);
    go

    alter database tlogspace set recovery full;
    go
    backup database tlogspace to disk='nul';
    go

now switch to that database, create a table, and run DBCC sqlperf(logspace) to check the size oand free space in your log file.

use tlogspace
go

create table data(id int identity(1,1), col varchar(8000))
dbcc sqlperf(logspace)

On my system I have logfile size of 0.9921875 and log space used (%) of 48.4245. Now insert some data into the table and run DBCC sqlperf(logspace) again. On my system 45 rows inserted gave desired results(number of rows inserted may need to be adjusted).

insert into data(col)
select replicate('a',8000)
go 45 --may need to adjust number
dbcc sqlperf(logspace)

This time, the DBCC sqlperf output should show that log size is the same but log space used is just under 100%. In this case, SCOM would probably fire an alert that log space is low. There is no more activity going on to cause the log file to grow and (in this example) no tlog backup to free up used space. tempdb is in simple recovery, so your active transaction probably used up most of the available space and did not release it but there was not enough activity in tempdb to trigger log file growth thus causing the alert fire.

cleanup database when finished

use master
drop database tlogspace


Restore previous version of database in sql server I need your help on restoring previous version of database on SQL Server.

In last week I restored database 'A' in DEV server with it's production copy and didn't realize that this database 'A' in DEV was not backed up and there was some already development work going on which is lost now!

Is there anyway of restoring previous version of 'A' database in DEV server?

Any help or suggestion would be really great!

Thank you!

There is a chance if the answer to all the below questions is NO

Was the database name exactly identical in Prod and Dev?

When restoring Database A on the Dev server, did you explicitly choose to move the data and the log files to the same path (and overwrite them) where the original dev Database A resided?

Did you drop database A on the Dev box before restoring from the prod backup?

If the answer to all the above questions is No, then there is a chance that your restore did not overwrite the original files of database A on the dev server.
When restoring a database from a backup that was taken on a different server/DB, SQL Server creates data and log files on the same path as that of the original database (in your case prod copy of database A) that the backup belongs to (unless explicitly mentioned in the MOVE clause of the RESTORE command). So if the path of the data and log files on Prod is different from the Dev box, then the restore operation will not overwrite the files unless you explicitly specify WITH MOVE 'filename' TO 'path' in the restore command along with the option REPLACE.
In this case, check the local drives on the dev server and see if the files of the original Database A are still there (assuming the path of the files on database A is different on the dev box than on the prod). If you succeeded in finding the orphan files, and you can attach them as a different database (eg: A_dev).


Index-refresh in SQL Server Is there any way to identify when a specific index was last rebuilt; unless it is used as a batch maintenance activity?

In SQL Server 2008R2 or in advanced versions.

You can only know when the Statistics were last updated using STATS_DATE.

You can use either of these queries:

SELECT name, STATS_DATE(object_id, index_id), *
FROM   sys.indexes
WHERE  name LIKE 'index name'

or

SELECT name, STATS_DATE(object_id, index_id), *
FROM   sys.indexes
WHERE  OBJECT_ID('schema.table') = object_id

When you rebuild an index, it updates its statistics:

  If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view, and the following query returns the same results as the preceding query. If statistics do not correspond to an index, they are in the sys.stats results but not in the sys.indexes results.


Dump specific tables from DB2 database Is there any command line tool that I can use on a DB2 database to dump a specific set of tables?

The problem is that I work in an application that its database tables was created in an existing schema alongside another application tables what would cause a lot of concurrency with these applications.

So after long discussion with our client he finally decide to attend our request to separate our database server since our application grows very large (tables with 14kk rows) and have lots of simultaneous request.

I've searched through DB2 documentation without any success. All the methods I've found out doesn't seems to solve this specific problem.

I read about the Export command. And couldn't find anything the do what we need.

Thanks in advance.

Edit

As asked in comments dump a specific set of tables means that we need to dump some tables from the database not all of then. Say that we have on this database about 5k tables and about 200 of then belongs to our system, so how would we dump only those 200 tables?

On the Export command documentation (mentioned above) there is no mention about exporting a group of tables only a data set (through a SQL command) of tables which does not meet the requirements.

Check out db2move - it uses export under the covers and offers options to load or import them into the target.

Details are documented here
db2move in KnowledgeCenter

An alternative - for those large volumnes - might be a "load from cursor".
Once you have created the new database and its objects (db2look) can help here you load the data into the new database. For this solution you have to create individual statements for each table.
Check out this article about load from cursor


Temporarly disable cache in oracle 11g I want to disable the caching for my session in oracle 11g to see how long a query takes.

Is this possible or is there a better way for meassuring the time a query takes to run?

Oracle caches (placed in System Global area) can not be turned off. You can only increase or decrease the size of these caches.


SSIS Package - Error Exporting Data to Flat File - Invalid Character I have an SSIS package where I pull data from a 3rd party source into a CSV file.

I get this error:

"Data conversion failed. The data conversion for column "Description" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Cannot copy or convert flat file data for column "Description".

SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Flat File Destination" (20300) failed with error code 0xC02020A0 while processing input "Flat File Destination Input" (20304). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure."

There seems to be an invalid character causing this issue...

I tried to remove it using this code but still an issue...

You have to deal with the unicode replacement character by calling it what it is:

Row.Description = Row.Description.Replace(NCHAR(65533), "a")

EDIT:

I apologize as I first gave you a pseudo-SQL Server solution, but with some additional research learned that SSIS behaves a little differently. I'm linking my source that gives multiple solutions, and for an answer will include the first. It seems that you could add a derived column component that would allow you to add a new column (e.g. strDescription from your table using the Type Cast operator DT_STR which will convert the unicode to string. You should then be able to use this derived column in place of the original column in you CSV destination. source Hope that helps!