texts: Linked server in Sql Server 2014 invalid path

texts:
I am trying to create a linked server to foxpro tables using vfpoledb, but it gives me the error of "Invalid path or file name" when trying to put as source \\xxx.xxx.x.x\same_names\abc (xxx.xxx.x.x represents the server's IP address).

However, I have no problem trying with the parent folder: \\xxx.xxx.x.x\same_names.

The only thing that occurs to me is that there may be some limitation as to the length of the path, is there really any limitation or is it that I am doing something wrong?

Here is the script of the create linked server

EXEC master.dbo.sp_addlinkedserver @server = N'BORRAR', @srvproduct=N'A', @provider=N'VFPOLEDB', @datasrc=N'\\xxx.xxx.0.8\DOCS\ION_ANDER\JAB\'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BORRAR',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'BORRAR', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


texts: How to select rows with a particular rank per partition?

texts:
I want to pick the row with the highest value in a column based on partitioning on another column. The problem is to pick the highest-earning employees from each department, click here for the complete problem.
The tables are:

Employee table:

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table:

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

The objective is to pick employees who have the highest salary in each of the departments.
My query:
select department, employee, salary
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank
      from employee e
      join department d on e.departmentid=d.id
      group by d.id
      having salary_rank=1) temp;

Error:

You cannot use the alias 'salary_rank' of an expression containing a window function in this context.'

How can I use a particular rank in the having clause in order to get the highest ranking employee in each department?
I am using the where clause that works:
select department, employee, salary
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank
      from employee e
      join department d on e.departmentid=d.id) temp
where salary_rank=1;

but I wanted to use the having clause.
I have tried reading the documentation and watched a couple of tutorials, all of them use the having clause for ranking per partition. What am I missing here?


texts: Elegant Code to find continuous time period and generate id

texts:
I have a table with two fields: time and value, both of them are integers and I want to find the continuous period with values bigger than 0 and then label the "value>0" period with continuous integers.

For example, if I have the input table like this:

I'd like the output table like this:

Using three CTE and two row_number() functions, I was able to do this. But I find the query too cumbersome. Does anyone have a more elegant code to do this?

I'm using sql server 2016 developer edition.

Here is my code:

CREATE TABLE #test1(
    [time] [int] NULL,
    [value] [int] NULL
)

insert into #test1
values(1,0),(2,0),(3,1),(4,0)
    ,(5,1),(6,2),(7,0),(8,0)
,(9,1),(10,2),(11,3),(12,0)
,(13,0),(14,0),(15,1),(16,0);

;with a1 as
(select *, [time] -  row_number() over (order by [time]) as group_num
    from #test1
where value>0),
a2 as
(select distinct group_num from a1
),
a3 as
(select group_num, row_number() over (order by group_num) as group_id
from a2)
select a1.*, a3.group_id
    from a1 left join a3
on a1.group_num = a3.group_num


texts: What is the most efficient way to merge topics of a forum or blog?

texts:
Lets say we have a topic table, and we want the ability to merge topics, such that all posts from x topics about the same thing will appear in one single topic( the oldest one). So, when someone clicks on a redundant topic that was merged, they'll be brought to the oldest one, with all the posts from the newer one included.

My idea was to create a merge table, that contained the following attributes: merge_id(which is a primary key, serial type, automatically incremented), topic_id, and date. So if five posts were merged, they'd have one record each in the merge table, all associated by the merge_id. Each topic would have a merge_id in its table as well, set to null if its not merged with another post.

However, lets say I need to merge another one with those five, I'd have to delete those five and create a new record for each, including the sixth one so that they'd all have the same merge_id, then update the merge_id on all the merged topics; all of this within a transaction.

This feels a bit cumbersome to me, and I wanted to know if someone knew of a much better way of implementing merging forum topics/threads.


texts: How to find the sum and overpayment of three table values

texts:
I need to find the each client sum of TotalAmount in Invoice_TBL and sum of TotalPaid in Payment_TBL. The below i have mentioned three tables.

Retrieve query answer should like below image from the above 3 tables.


texts: Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

texts:
If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example:

$ mysql -u root -p --column-type-info
Enter password:

--- Copyright and help message snipped for brevity ---

mysql> select cast(1 as signed integer);
Field   1:  `cast(1 as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG            <== LONGLONG i.e. 64 bit integer
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+---------------------------+
| cast(1 as signed integer) |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

I would have expected the return type from that cast to be a LONG (32 bit integer).

If I select a column from a table that has an INT I see that it is indeed just a LONG:

mysql> describe contact;

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| contact_id | int(11) | NO   | PRI | NULL    | auto_increment |

      == remainder of table snipped ==

mysql> select contact_id from contact where contact_id = 20;
Field   1:  `contact_id`
Catalog:    `def`
Database:   `centreon`
Table:      `contact`
Org_table:  `contact`
Type:       LONG                     <== LONG i.e. 32 bit integer
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

+------------+
| contact_id |
+------------+
|         20 |
+------------+
1 row in set (0.00 sec)

mysql>

If I cast the same column to a signed integer, I again get a 64 bit integer returned:

mysql> select CAST(contact_id as signed integer) from contact where contact_id = 20;
Field   1:  `CAST(contact_id as signed integer)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+------------------------------------+
| CAST(contact_id as signed integer) |
+------------------------------------+
|                                 20 |
+------------------------------------+
1 row in set (0.00 sec)

There is a similarly reported issue here:

  http://bugs.mysql.com/bug.php?id=64084

But sadly the OP doesn't get a straight answer.

Is this a bug in the CAST() function or is this by design?


texts: Create a view that summarize vehilce owned from two tables cars and bikes

texts:
I have 3 tables owner, car and bikes where car and bike are dependent on owner.

owner(_name,_ssn)

cars(_ownername,_ownerSSN, model)

bikes(_ownername,_ownerSSN, model)

I want to create a view that shows how many cars and bike I own
in the following way

vehicle_owned (_name, _ssn , vehicleamount)

where vehicle amount is car and bikes summarized.

I've tried using count with join on the tables but nothing seems to work


texts: Parse hyphenated word to unsigned integer

texts:
When tested to_tsvector on 'someword-123' with english full text config, I got the following result:

SELECT to_tsvector('someword-123');

to_tsvector
tsvector
--------------------
"'-123':2 'someword':1"

However, I want to config to get the result like "'123':2 'someword':1" so when user search with 'someword & 123', it could return result.

Is there anyway to do this? Hope someone could help.

Update: For more information, my full text search is applied mostly for product name. For example, I have a product which name is "Intel Core i7-3820 Processor" and I want the result of to_tsvector() for this string could be like "'intel':1, 'core':2, 'i3':3, '3820':4, 'processor': 5" (currently, it's "'intel':1, 'core':2, 'i3':3, '-3820':4, 'processor': 5") so this product can be returned when user search with 'intel 3820'

Thank for your all.


texts: How postgresql full text search disable file parser?

texts:
select to_tsvector('english_nostop', 'u,s') @@ to_tsquery('english_nostop', 'u<->s');

return t, while
select to_tsvector('english_nostop', 'u.s') @@ to_tsquery('english_nostop', 'u<->s');

return f
Debug with this:
SELECT alias, token, lexemes FROM ts_debug('english_nostop', 'u.s');
alias | token | lexemes
-------+-------+---------
file  | u.s   | {u.s}

and
audiobook=> SELECT alias, token, lexemes FROM ts_debug('english_nostop', 'u,s');
alias   | token | lexemes
-----------+-------+---------
asciiword | u     | {u}
blank     | ,     |
asciiword | s     | {s}

How can remove such behavior. To make . like ,?   The alias file seem to think u.s as special one.
Here is my configure:
CREATE TEXT SEARCH DICTIONARY english_stem_nostop (
Template = snowball
, Language = english
);

CREATE TEXT SEARCH CONFIGURATION public.english_nostop ( COPY = pg_catalog.english );
ALTER TEXT SEARCH CONFIGURATION public.english_nostop
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, hword, hword_part, word WITH english_stem_nostop;
ALTER TEXT SEARCH CONFIGURATION public.english_nostop DROP MAPPING FOR asciihword, hword;

And we can't simply remove file, this will remove the total text:
ALTER TEXT SEARCH CONFIGURATION public.english_nostop DROP MAPPING FOR file;
SELECT alias, token, lexemes FROM ts_debug('english_nostop', 'u.s');
alias | token | lexemes
-------+-------+---------
file  | u.s   |

Which is wrong too.
And if we ALTER MAPPING for file, we got
SELECT alias, token, lexemes FROM ts_debug('english_nostop', 'u.s');
alias | token | lexemes
-------+-------+---------
file  | u.s   | {u.}

Still not my purpose.


texts: Database design/structure for address details

texts:
I am trying to design database. I have one problem related to addresses. The will be different types which will have addresses, for example Companies, Persons.
I am not sure how to design my database. I have three options in my mind.

Option one: Create one Address table and create AddressRelation tables for each of above tables like below:

  1) Addresses table:

  ID
  Country
  City
  District
  Street
  Building
  Apartment

  2) Companies table:

  ID
  CompanyName
  ..........

  3) Company address relation table:

  ID
  CompanyID (Has foreign key to Companies table)
  AddressID
  AddressType (Legal, Factual) Status

  4) Persons

  ID
  Surname
  FirstName
  Patronymic
  ..........

  5) Persons address relation table

  ID
  PersonId (Has foreign key to Persons table)
  AddressID
  AddressType (Legal, Factual, Birth)
  Status

Option two: Create only one AddressRelationTable and add trigger instead of foreign keys:

  1) Addresses, Persons, Companies table as above.

  2) Address relation table:

  ID
  OwnerID (OwnerID can be PersonID or ComanyID)
  AddressID
  AddressType (Legal, Factual, Birth)
  Status

Option three: Add foreign keys from Persons and Comapnies table to Address table:

  1) Address table is as above.

  2) Companies table:

  ID

  CompanyName
  ..........
  LegalAddressID (Has foreign key to Addresses table)
  FactualAddressID (Has foreign key to Addresses table)

  3) Persons table:

  ID
  Surname
  FirstName
  Patronymic
  ..........
  BirhtAddressID (Has foreign key to Addresses table)
  LegalAddressID (Has foreign key to Addresses table)
  FactualAddressID (Has foreign key to Addresses table)

Third option is my last option, because it is less flexible one and I don't want to add address details directly to my main tables.

Option two is good because using this I need to create less tables, but I don't want to use trigger instead of foreign key, and OwnerID can be from two tables.

First option is straightforward I think, but this time number of tables will increase significantly.

Please consider that there are actually three tables which will have address details, same scenario must be applied for contact details and this number can increase in the feature.
I tried to keep question as simple as possible so I don't provide details for third table and for contact details.

My question is which option should I use and why? May be there are other great options which I don't imagine?


texts: How to increase sql server performance? denormalization (making cascade delete off) vs making duplicate data

texts:
I have a database with following related table ( sql server enterptise edtion 2014)

each state have many citites which each city has may municpalArea and so on

   State => Cities = > MuniciplArea => CallCenter => CallCenterCode => **LandLine**

Then I have a LandLine table which is required to be searchable by the value from the above tables e.g

   select from landline where stateId =1

The main requirements is performance

The two solutions that I have in mind are:

using relationship in landline table and making cascade delete off because of circular reference so i would have something like :

    landline (table)
        MunicipalAreaId  , CallCenterId , StateId,CityId, CallCenterCodeId

the other solution is cutting the relationship between tables so my landline table is like

    landline (table)
        MunicipalCode  , CallCenterCode , StateCode ,CityCode , CallCenterCode

both of these solutions are provided to avoid join in order of better performance.

maybe joining these tables doesn't have that much of impact on performance that I'm afraid of so how should I design tables?

should i go with either of the above solutions? ( which one)

or should i go with joining tables ? ( is performance good enough? )

is there any alternative solutions?

Update:
e.g Joins that are needed to select landlines from the same state

   SELECT *   FROM     LandLines AS [Extent1]
      INNER JOIN  CallCenterCode  AS [Extent2] ON [Extent1].[SabetCallCenterCodeId] = [Extent2].[CallCenterCodeId]

      INNER JOIN [dbo].[CallCenter] AS [Extent3] ON [Extent2].[CallCenterId] = [Extent3].CallCenterId]

      LEFT OUTER JOIN [dbo].[MunicipalArea] AS [Extent4] ON [Extent3].[MunicipalAreaId] = [Extent4].[MunicipalAreaId]

      INNER JOIN [dbo].[City] AS [Extent5] ON [Extent4].[CityId] = [Extent5].[Id] WHERE 63 = [Extent5].[StateId]


texts: Mysql multisource replication : on slave map masters dbname to something like mastername-originaldbname

texts:
I need to implement a mysql multisource replication using mysql >=5.7.11, there are already a certain number of mysql servers running which will become masters for the single slave which is going to be installed soon.
The main problem is that each master has same db name and I would like to avoid conflicts in slave and keep each db replica separated.
Is there a way to configure the slave to, for example, put master name as prefix of its dbname?
I mean something like this :
master1.db
master2.db

Or do I really need to choose one of the following :
- Change db name on masters
- Install one slave server for each master
?

Thanks in advance
Best regards
magowiz

UPDATE :
As described in comments, in short :

I also tried something like that, using a solution borrowed by mariadb that I discovered it's not compatible with mysql : mariadb.com/blog/… . I write something like :

[mysqld]
master1.replicate_rewrite_db=db->db1
master2.replicate_rewrite_db=db->db2

with master1 and master2 the two replication channels but it didn't work , I only obtain replication of first instance db to db1 . Is there a way to use replicate_rewrite_db directive on channel/master base ?

The answer I gave to myself it is NO , you can set up multisource replication, you can rename every replicated db on slave specify the mapping but this directive cannot be related to channel, so you need to have unique db names across all masters in order to make multisource replication work.

Currently it seems that mariadb implements this and mysql not.


texts: Connect to Oracle RAC from SQL Developer?

texts:
We have set up RAC database on server. We can connect to database from sqlplus on the server using SCAN address. However, if I try to connect to RAC using SQL developer from client machine using SCAN address-based TNS entry, it shows the error

12-APR-2016 22:33:07 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=User))(SERVICE_NAME=racdb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.227.110)(PORT=63694)) * establish * racdb * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack

Client machine is behind the firewall.
Here is the TNS entry.

RACDB =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.149)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.150)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.151)(PORT = 1521))
        )
    (CONNECT_DATA =
      (SERVICE_NAME = racdb)
    )
  )

We cannot use DNS to resolve SCAN IPs from client side, so we used the IP address on the client machine. And IP addresses of SCAN are converted on client side using NAT. Below is their real addresses set on the server

# SCAN
#192.168.227.149 racdb-scan.esplink.mn racdb-scan
#192.168.227.150 racdb-scan.esplink.mn racdb-scan
#192.168.227.151 racdb-scan.esplink.mn racdb-scan

If we change the IP address of TNS entry to VIP addresses, the connection is successful and I can connect to RAC database.

Is it wrong to use SCAN addresses to connect to RAC?


texts:
I am running a large local database with MySQL and InnoDB. The size is around 30GB for data and 6GB for Keys. The machine has 16GB of RAM.
The table looks like this
symbol VARCHAR(10),
iso_datestamp VARCHAR(14),
-- a lot more columns with values with mostly DECIMAL(26,6)s

I have BTREE indexes on the symbol and the iso_datestamp.
The operations I run are mostly updates which are run like this:

DELETE FROM table WHERE symbol = 'XXX'
INSERT INTO table (columns) VALUES (...)

This needs to be done to avoid data inconsistencies as opposed to just appending data.
I also frequently run analysis as in
SELECT FROM table WHERE symbol in ('XXX', 'YYY', ...) [AND iso_datestamp > '2015...']
Per symbol I have around 3000 entries.
The performance problem: As soon as the table starts to grow over around 10GB, the DELETE and INSERT statements slow down substantially from usually less than 0.4 seconds to well over 1 second even up to 5 seconds and more as the table keeps growing. RAM is not limiting (according to top).
I need the total of DELETE and INSERT to be around 1 second on average (of course faster is also fine)...
I am having 2 ideas right now:

Using hash indexes instead of btree indexes, since I am only doing equality operation
Partitioning the table using the symbol and the range

Are those viable strategies? Do both, just one? Other things I should do to improve the speed?


texts: Possible to CROSS JOIN a subquery without duplicating code?

texts:
I'm wanting to get all permutations between two columns in the same table.

Currently my query is this:

declare @tgt int = 123456;
select *
from (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF1
cross join (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF2;

Note that subquery WF1 is the same as WF2. In order to be DRY, I'd really like to do this:

declare @tgt int = 123456;
select *
from (
  select T.id, T.parentId
  from Task T
  where (
    (T.id = @tgt and T.typeId = 3)
    or
    (T.parentId = @tgt and T.typeId = 1)
  )
) WF
cross join WF;

But this is not working in SQL Server 2012.

Is there some technique I can apply to avoid duplicating the query?


texts: optimize table in stored produce using phpMyAdmin causes out of sync error

texts:
I get the following error when running the code in phpMyAdmin. NOTE: I can run any other sql code (update, insert, replace) via phpMyAdmin in a stored procedure; but an optimize causes issues.

NOTE: This code works fine when running from CLI.

ERROR: #2014 - Commands out of sync; you can't run this command now

SQL Code:

delimiter //

create procedure update_phppos()

begin
OPTIMIZE table phppos_items;
end;

//

delimiter ;

-- Execute the procedure
call update_phppos();

-- Drop the procedure
drop procedure update_phppos;


texts: Azure SQL Row Level Security Full Index Scan

texts:
We have implemented SQL Row-level security using SESSION_CONTEXT. From an architecture standpoint, this is a beautiful paradigm that allows our application to be clueless to the fact that only 1 tenant's data is being returned based on the logged in user.

However, we've come across some performance issues related to pre-filtering joins that result in full table scans or index scans. When I explicitly add FORCESEEK, the queries run significantly faster but SQL will never choose a seek on its own and it's my understanding that RLS is to blame for this. I verified that parameter sniffing and cached statistics are not to blame by forcing a recompute every time.

Have you had this happen before? Is there a way around it with RLS? Our backup plan is to scrap RLS completely and move to an Azure SQL elastic pool and horizontal sharding based on customer id.

Thanks so much for any guidance you can provide.

UPDATE: Below are the query execution plans for:
1) With RLS enabled on all 3 tables
2) With RLS enabled on all 3 tables + FORCESEEK option
3) With RLS disabled


texts:
I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong.

I have three tables, one stores students basic info ---{students_info}, the other stores their classes ---{students_class} and the other stores their positions ---{students_positions}.

students_info
 st_id | f_name | l_name
  1    | quinoo | mickel
  2    | nunoo  | gyan
  3    | kwanis | nnipa

students_class
 cl_id | class | academic_year |st_id
  13   |  A    | 2013          | 2
  32   |  B    | 2014          | 1
  73   |  C    | 2014          | 3

students_positions
 pos_id | positions | ushered_year | st_id
   54   | President |   2013       |  2
   34   | secretary |   2014       |  3

This is what I have done so far. But a friend saw my tables and its relationships and he was like, it would be best, if I created another table to contain all the ids column values in addition to the table I have. This is what he suggested:

students
 all_std | st_id | cl_id | pos_id
 01      |  1    |  32   |  null
 02      |  2    |  13   |  54
 03      |  3    |  73   |  34

UPDATE

My friend was suggesting the additional table above (of which i think is wrong) because I was querying data from all the three tables within just a query statement using JOIN, but the data appears in duplicate. Look at the one below

SELECT
    a.st_id
    b.class
    b.academic_year
    c.positions
FROM
    students_info a
      INNER JOIN
              students_class b
          ON
              a.st_id=b.st_id
      INNER JOIN
                students_positions c
          ON
               a.st_id=c.st_id
WHERE
      a.st_id=2;`

This is the answer i receive after executing the query

 st_id | class | academic_year | positions
   2   |  A    |    2013       | President
   2   |  A    |    2013       | President

On the contrary this is what I expect

 st_id | class | academic_year | positions
   2   |  A    |    2013       | President


texts: How to backup a MySQL database without slowing the application?

texts:
I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is running our application response time goes through the roof. So every 8 hours, the application is slow and sometimes even produces errors.

Is there a way to backup a MySQL database with MyISAM tables without slowing application code and still get a consistent backup?


texts: Management Studio Database Space Available

texts:
Recently I have installed a fresh version of sql server 2016 RC. I get an error when I try to import a bacpac file from azure.

  SQL72012 Generate drop statements for objects that are in the target database but that are not in the source' check box...

  Could not allocate space for object ‘’ in database ‘’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I see that "Space Available" field in the Database properties shows 0,87 Mb. I have more than 1 TB available in the local server, so how can I increase this value or fix the above problem?


texts:
I have RMAN backup from AIX server and want to restore on Linux OS. Could you please let me know if there is a way to do it?

Thanks a lot for help in this regard.


texts:
I'm not sure if this is the best way to migrate data to another collation but I've done the following:

Scripted the source database and changed the collation in the script
to my required one
Ran the script and created the new database with the wanted
collation (along with all the tables,triggers,indexes etc.
I tried to 'Export Data' from the SQL Server management studio but I
got an error related to code page differences.
Since Export/Import Data didn't work, I resorted to using bcp to
mass export all the tables in the source database to txt files.
After that I began importing the rows using bcp but I saw some
errors related to truncation and invalid characters.

What's the best/proper way to migrate the data from a database with a different code page & collation after creating the clone from the scripted source?


texts: Postgresql Replica is in sync but repmgr says node unattached

texts:
I've set up a replica of a Postgresql instance using repmgr, and the replica is totally in sync with the master instance and have been in sync since some days, but repmgr tells me replica isn't connected.
This is what the cluster looks like:

postgres@www:~$ repmgr cluster show
 ID | Name             | Role    | Status    | Upstream    | Location | Connection string
----+------------------+---------+-----------+-------------+---------+------------------------------------------------------------------
 1  | orig_master      | primary | * running |             | default  | host=MASTER user=USER dbname=repmgr connect_timeout=2
 2  | orig_slave       | standby |   running | orig_master | default  | host=SLAVE user=USER dbname=repmgr connect_timeout=2

This is what we get on the replica instance:

postgres@db:~$ repmgr node status
Node "orig_slave":
    PostgreSQL version: 9.6.11
    Total data size: 5603 MB
    Conninfo: host=SLAVE user=USER dbname=repmgr connect_timeout=2
    Role: standby
    WAL archiving: disabled (on standbys "archive_mode" must be set to "always" to be effective)
    Archive command: rsync -a %p barman@BARMAN:/mnt/volume/prod/incoming/%f
    WALs pending archiving: 0 pending files
    Replication connections: 0 (of maximal 6)
    Replication slots: disabled
    Upstream node: orig_master (ID: 1)
    Replication lag: 0 seconds
    Last received LSN: 63/BB0E3C10
    Last replayed LSN: 63/BB0E3C10

Which clearly shows that we have no relpication lag and instances are in sync, and WAL numbers are correct too.
This is what I get on the master instance:

postgres@www:~$ repmgr node status
Node "orig_master":
    PostgreSQL version: 9.6.11
    Total data size: 5603 MB
    Conninfo: host=MASTER user=USER dbname=repmgr connect_timeout=2
    Role: primary
    WAL archiving: enabled
    Archive command: rsync -a %p barman@BARMAN:/mnt/volume/prod/incoming/%f
    WALs pending archiving: 0 pending files
    Replication connections: 1 (of maximal 6)
    Replication slots: disabled
    Replication lag: n/a

WARNING: following issue(s) were detected:
  - 1 of 1 downstream nodes not attached:
    - orig_slave (ID: 2)

HINT: execute "repmgr node check" for more details

Which tells me that downstream node isn't connected, which is clearly connected!
Now, my question is: Is there actually a problem in replication process? If yes, then What is it and how can I solve it? If not, how can I make repmgr sure that there is no problem?

P.S.: Postgresql 9.6.11, repmgr 4.2


texts: Availability Group Listener - Dedicated Server?

texts: Should an AG Listener be set up on it's own server? If so, does it require it's own SQL Server license?


texts: Getting generate_series(date, date) does not exist

texts:
When I run commands like this,

SELECT * FROM generate_series('YESTERDAY'::date, 'TODAY'::date);
SELECT * FROM generate_series('YESTERDAY'::timestamp without time zone, 'TODAY'::timestamp without time zone);
SELECT * FROM generate_series('YESTERDAY'::timestamp with time zone, 'TODAY'::timestamp with time zone);

I am getting errors like this,

ERROR:  function generate_series(date, date) does not exist
ERROR:  function generate_series(timestamp without time zone, timestamp without time zone) does not exist
ERROR:  function generate_series(timestamp with time zone, timestamp with time zone) does not exist

I also get this same error when I try to generate a date series with timestamp without time zone


texts: Include single quote string in redshift dynamic SQL

texts:
I have function named
Schema.func_date(date,varchar)

I wanna do like this(I am using below statement inside Store proc)
Execute 'Select *, ID as Main_Id, ' ||
         'schema.func_date(quote_literal('2020-02-20'),quote_literal('ST')), '||
         'from main_table'

I am getting Invalid operation error while passing single quote string. How to pass the single quote string perfectly in execute statement?


texts: MySQL slow server in a Billing application

texts:
i have on server DELL R220 with 8Gb Ram and 60GB HDD CPU 2.8Mhz

anyway, this server is used to host the Radius Manager one of the telecom billing servers build on FreeRadius and with CentOS 6.

we have only 200 users, meantime we have more than 2 million voucher generated in the system and stored in the database.

the issue that when any inquiry is made to the users table the response time is very long and i do not know why.

i'm not expert and would appreciate you help to address the issue.


texts: Primary file after table partitioning - SQL Server 2014

texts:
I have a 230GB database without a clustered index that I'm trying to partition into 25 files for efficiency on a hard drive with 460GB of space. I've created the partition function and partition scheme, and I have executed the commands. During the partition process, the 25 files grew to about 230GB as well and the Primary file did not shrink causing the drive to run out of room. Although the query finished, I was left with two errors.

Transaction log disappeared since there was no room due to ACTIVE_TRANSACTION
Could not allocate a new page for database because of insufficient disk space.

I'm trying to figure out if:

the partition is successful and that this is an indexing issue.
the Primary is still taking up the full 230GB or is it just allocated space that appears used on my drive. If so, can I safely shrink this without fragmentation?
there is an issue with the missing transaction log. If not, where is it going?

I've been able to run queries against the database, so it seems fine on the surface. However, I cannot find any more information on the above mentioned problems. In theory, SQL cannot operate without a transaction log, so I am not sure how to proceed.

I only have a single table Europe_Amad which has 130 columns. No primary keys set.

My partition script is as follows:

USE [FTC]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [FTC_to_NACE](nvarchar(255)) AS RANGE RIGHT FOR VALUES (N'3500', N'3600', N'4100', N'4500', N'4800', N'5500', N'5800', N'6400', N'6800', N'6900', N'7700', N'8400', N'8500', N'8600', N'9000', N'9400', N'9700', N'9900')

CREATE PARTITION SCHEME [FTC_PS1] AS PARTITION [FTC_to_NACE] TO ([FTCC], [FTCD], [FTCE], [FTCF], [FTCG], [FTCH], [FTCI], [FTCJ], [FTCK], [FTCL], [FTCM], [FTCN], [FTCO], [FTCP], [FTCQ], [FTCR], [FTCS], [FTCT], [FTCU])

SET ANSI_PADDING ON

CREATE CLUSTERED INDEX [ClusteredIndex_on_FTC_PS1_636522495640236401] ON [dbo].[Europe_Amad]
([NACE_PRIM_CODE])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [FTC_PS1]([NACE_PRIM_CODE])

DROP INDEX [ClusteredIndex_on_FTC_PS1_636522495640236401] ON [dbo].[Europe_Amad]

COMMIT TRANSACTION


texts: What's a way/ways to return Identity element (that is auto-generated) in a table from a stored procedure?

texts:
I have tables in my db with auto generated primary keys that I want to get a hold of when inserting new rows from my Java calling code. I'm trying to create stored procedures that handle the insertion and return back the primary key (ie. the auto-gen key) back to the calling code. I'll only be inserting one row at a time.

I have come across the SCOPE_IDENTITY() function but it seems to not be working inside a stored procedure. I most probably am using it in an incorrect way so that may also be the problem.

Here's what I put together from different sources. This doesn't compile and is in no way a correct way to do it. I'm a newbie, so it may seem like someone trying to communicate in a broken language. Please bear with me.

CREATE OR REPLACE PROCEDURE SAVER(IN PARAMETER1 VARCHAR(30), OUT ID_PARM BIGINT)
BEGIN
DECLARE v_PARAM VARCHAR(30);
DECLARE v_ID BIGINT;
SET v_PARAM=PARAMETER1;
INSERT INTO TAB_1 (NAME) VALUES (v_PARAM);
SELECT SCOPE_IDENTITY() INTO v_ID;
SET ID_PARAM=v_ID;
END

Another thing I'm worried about is I read somewhere that Java and DB2 don't support out parameter so I'm not sure how to return back this value.


texts: Query against a view vs. query against the underlying base table

texts:
I was told to query against views in our ERP system because this would prevent any locks put on the actual tables thus blocking actual work being done. After learning a bit more about SQL Server, I'm not sure this is accurate but wanted to get clarification.

Knowing that a view is just a “saved query”, it appears I'm querying the base table via the view so it would seem this wouldn't accomplish anything as far as preventing blocking.

In other words if I do the following and leave these open, am I understanding that either one would block sales table writes?

begin trans
select * from dbo.view_sales

versus

begin trans
select * from dbo.sales

Thanks!


texts: Looking for advice on how to integrate data from 100+ client DB's into a centralized reporting database

texts:
I am a SQL Developer (not DBA or Architect) for a small (~50 employees) SaaS company. I am tasked with figuring out how to:

Offload operational reporting from our 100+ OLTP databases
Allow those reports to run against data from multiple client databases
Position our company to provide more analytics-based solutions in the future

I have read a number of articles on various technologies like transactional replication (specifically the many-to-one/central subscriber model), SQL service broker, log shipping, Change Tracking (CT), and Change Data Capture (CDC, my understanding is this is enterprise-only), and I am not sure what path is best to pursue.

I am hoping some of you with integration expertise may have encountered a setup similar to ours and be able to point me down a successful path or direct me to some resources that would be helpful.

Due to cost constraints, our solution must work within SQL Server Standard Edition. Also, the solution must be reasonable to support/maintain within our small organization.

Basic configuration:

We currently have 100+ individual client databases, most deployed on SQL servers at our data center, but some deployed on client servers within their data center that we can remote into. These are all SQL Server 2008 R2 databases, but we are planning to upgrade to SQL 2016 soon.

We use database projects and dacpacs to ensure the schema is the same across all client databases that would be integrated. However, since we do not force all clients to upgrade to new versions at the same time, some schema differences are possible between upgrades. The solution must be flexible enough not to break if client A is on software version 1.0 and client B is on version 1.1.

Operational reports are currently run directly from each client's OLTP database. We are concerned about the impact this will have on the application's performance if we do not offload it.

High-Level Requirements:

Our clients are hospital sterile processing departments (SPD’s) who want up-to-the-moment reports on what they’ve processed so far, where inventory is, etc. SPD's process inventory around the clock, including weekends and holidays. Since one of the main purposes of this effort is to better support operational reporting, we would like the data to be as close to real-time as possible to continue meeting clients’ needs.

Currently we have some SPD’s in separate databases that are actually part of the same hospital system. These clients want the ability to report against all the SPD’s in their system.

Strategically speaking, we would like the ability to easily aggregate data across all our clients to support our internal analytics initiatives. Our expectation is that we would be able to use the collected operational data as a source for data marts/warehouse.

Thoughts so far:

Transactional replication seems like it would provide the most "real-time" solution. I found this response to be especially helpful, but I am concerned that with the potential for schema differences it will not work for us: SQL Server Many-to-One replication

Log shipping doesn't sound ideal given that the log cannot restore while queries are active. I either have to kick everyone out so the log can restore or the data will become stale. I am unclear as to whether this method could be used to centralize data from multiple databases, since each shipped log would only be for the individual database it came from.

Using SQL service broker, latency may be unpredictable if a queue were unable to keep up with the number of messages to process.

CT only identifies a version for each table row. Latency would be dependent on how quickly we could process something like an SSIS package against each database to retrieve the data and insert it in a central repository.

Do we need to consider replicating each database individually and then perhaps use some sort of data virtualization technique to combine data from the various replicated sources?

Any advice or direction you are willing to provide would be greatly appreciated.


texts:
When i try to connect to my postgres database, i get the error below:

  could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.2.1.96" and accepting TCP/IP connections on port 5432?

I make a ssh connection to the machine which runs postgres service, i get the error like below :

[omerkaraca@btpsndsrpg01 bin]$ systemctl start postgresql.service
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Authenticating as: Selman AY (say)
Password:
==== AUTHENTICATION COMPLETE ===
Failed to start postgresql.service: Unit postgresql.service failed to load: No such file or directory.
[omerkaraca@btpsndsrpg01 bin]$ systemctl status postgresql.service
● postgresql.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)
[omerkaraca@btpsndsrpg01 bin]$ ^C
[omerkaraca@btpsndsrpg01 bin]$ ^C
[omerkaraca@btpsndsrpg01 bin]$ service postgresql start 9.6
Redirecting to /bin/systemctl start  9.6 postgresql.service

How can i restart the postgresql service?


texts: Is capitalization of SQL commands/keywords required, expected or completely optional?

texts: Also, is it done automatically in some environments?


texts: Does SQL profiler affect server performance?

texts:
I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load.
I have found on the internet that SQL profiler can be used to track the database interactions which can later be analyzed to find the exact point where the problem occurs.
I have also found as to how we can start the sql profiler.
But now my question is that by starting the sql profiler am i going to affect the server performance?


texts: Dim/Fact Modelling: Dimension table for customer devices

texts:
The incoming event stream data contains information for customer phone devices:

Platform: Android or iOS?
Operating System: (version)
UDID
Phone Model

In the event stream, each event telemetry contains a pre-defined event (e.g. User swiped their screen, or, User clicked a button in app).
I'd like to model these information into a dimension table, but I'm pretty new to Kimball and reading his books for an afternoon doesn't help too much. I'm wondering if I'm doing the right thing here? Details in next paragraph.
I'm thinking about assigning each unique combination of (Platform, OS, UDID, Model) a surrogate key, and during ETL, I'll check to see if that unique combination is in the dimension table. If it's not there, the key self-increases and the combination is recorded. If it is, no change is made to the dimension table. The dimension table consists of 5 fields, a surrogate PK (join with fact table), and Platform, OS, UDID and Model.
Say we have the following data coming in:

UserID
EventName
...
Platform
OS
UDID
Model

111
Swipe_Up
...
Android
Android 8.0
123-345-678
Google Pixel

111
Click_UI
...
iOS
iOS 11.0
abcdefg
iPhone 11

200
Swipe_Up
...
Android
Android 8.0
123-345-678
Google Pixel

201
Swipe_Down
...
iOS
iOS 13.0
hijklmn
iPhone 12

201
Swipe_Down
...
iOS
iOS 13.0
NULL
NULL

230
Swipe_Up
...
iOS
NULL
NULL
NULL

300
Swipe_Up
...
Android
Android 8.0
NULL
Google Pixel

So you can see user 111 used two phones, so that should be two devices. User 200 has the same phone as User 111 (even same UDID) so that's not a new record for my dimension table. User 201's second row seems to be the same as his first row, but because of the NULLs it is technically a new device. Of course maybe business wants to assign some rules to say "Hi as long as a few fields are the same, we consider them as the same device". In total I should have 5 unique rows in my dimension table.
Does this make sense to you?


texts: Can only specific SQL users be forced to use TLS?

texts:
My school would like to develop custom software for some embedded devices. As part of this, we may need the devices to connect to an SQL Server instance.

Is it possible to force specific SQL users to use encryption when connecting to the server? We don't want to force it for the entire instance because of some important legacy software.

We're also evaluating other (frankly, better) solutions, but I want to know what's possible.


texts: Why is parallelism not good in this query?

texts:
I was looking for missing indexes in the cached execution plans then I found a very nice article by Jonathan Kehayias:

Finding Key Lookups inside the Plan Cache

and from there I borrowed the following script:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--IF OBJECT_ID('TEMPDB..#MissingIndexInfo') IS NOT NULL
--   DROP TABLE #MissingIndexInfo;

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
    n.query('.'),
    i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp,
    i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName,
    i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName,
    i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName,
    i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName,
    i.query('.'),
    STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
       FROM i.nodes('./OutputList/ColumnReference') AS t(cg)
       FOR  XML PATH('')),1,2,'') AS output_columns,
    STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
       FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg)
       FOR  XML PATH('')),1,2,'') AS seek_columns,
    i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') as Predicate,
    cp.usecounts,
    query_plan
FROM (  SELECT plan_handle, query_plan
        FROM (  SELECT DISTINCT plan_handle
                FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs
        OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
      ) as tab (plan_handle, query_plan)
INNER JOIN sys.dm_exec_cached_plans AS cp
    ON tab.plan_handle = cp.plan_handle
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n)
CROSS APPLY n.nodes('.//RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') as s(i)
OPTION(RECOMPILE, MAXDOP 1);

I noticed the MAXDOP 1 but I am not sure as how it can improve the performance of the query.


texts: Phantom Transactions using Version Store in tempDB

texts:
We are seeing version store space increasing on one of our servers, and there are ~20 sessions that are showing as actively accessing version store rows for over 24 hours. The problem is, when I try to kill the session_id's I get the following message:

"Msg 6106, Level 16, State 1, Line 1 Process ID 137 is not an active process ID."

These are the two queries I am running to view total version store size, and which sessions/transactions are actively accessing version store:

    --Total version store size
SELECT SUM(version_store_reserved_page_count) AS [version store pages used]
    ,(SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

--Shows all active transactions that generate or potentially access row versions
SELECT transaction_id
    , dateadd(SECOND,-elapsed_time_seconds, getdate()) 'start_time'
    ,elapsed_time_seconds
    ,session_id
    ,transaction_sequence_num
    FROM sys.dm_tran_active_snapshot_database_transactions;

What would cause these "phantom" sessions to access the version store, even though they are "not active"? Also, is there anyway to clean these up besides restarting SQL?

The application servers were bounced yesterday, and I'm thinking that maybe this somehow 'orphaned' these transactions?


texts: How to log all SQL queries for a single connection or user or client host?

texts:
Is there a way to log all SQL queries in Postgres 9.3.9 for a single connection or user or client host?

In other words, I want log_statement = all but only for a certain username or client hostname or client ip or connection id.

One option is to log SQL activity for all clients and search the logs. I'm hoping there's a better way.


texts: Find Mysql's Master Binary Log Position in Slave's Binary Log

texts:
I have a mysql master/slave replication setup and I like to know the binary position in the slave where it executed the query from the master. I have log_slave_updates enabled on slave. So, is this is the right process?

On the master, I do show master status and it says mysql-bin.000001, 45408621 (assume this is the position that I want to know its equivalent in the slave's binary log).

Then I do a mysqlbinlog on the master's mysql-bin.000001, and I look for that position 45408621 which will be this

   # at 45408621
   #190620 17:39:21 server id 118  end_log_pos 45408704 CRC32 0x481aab34
   # Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
   #  2b4e16d 39 27 0c 5d   02   73 00 00 00   53 00 00 00   c0 e1 b4 02   08 00
   #  2b4e180 9b 87 1b 00 00 00 00 00  02 00 00 27 00 00 00 00 |................|
   #  2b4e190 00 00 01 00 00 00 00 00  00 00 00 06 03 73 74 64 |.............std|
   #  2b4e1a0 03 02 00 01 00 04 21 00  21 00 21 00 05 06 53 59 |..............SY|
   #  2b4e1b0 53 54 45 4d 65 62 00 42  45 47 49 4e 34 ab 1a 48 |STEMeb.BEGIN4..H|
   #       Query   thread_id=1804187       exec_time=0     error_code=0
   SET TIMESTAMP=1561077561/*!*/;
   BEGIN

Then on the slave, I ran mysqlbinlog on its last binary log (eg. mysql-slave-bin.000001) and grep for the "17:39:21" and the thread id of 1804187, and I get this

   # at 12506
   #190620 17:39:21 server id 118  end_log_pos 12574 CRC32 0x0802ae3f
   # Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
   #     30da 39 27 0c 5d   02   73 00 00 00   44 00 00 00   1e 31 00 00   08 00
   #     30ed 9b 87 1b 00 00 00 00 00  00 00 00 1a 00 00 00 00 |................|
   #     30fd 00 00 01 00 00 08 00 00  00 00 00 03 02 00 01 00 |................|
   #     310d 04 21 00 21 00 21 00 00  42 45 47 49 4e 3f ae 02 |........BEGIN...|
   #     311d 08                                               |.|
   #   Query   thread_id=1804187   exec_time=0 error_code=0
   SET TIMESTAMP=1561077561/*!*/;
   BEGIN

Can I conclude that the master's position of (mysql-bin.000001, 45408621) is equal to the slave's position (mysql-slave-bin.000001, 12506) in the slav
e's binary log?

And if this is the correct process, is there a simpler way of doing this? Maybe someone has written some script already for this?


texts:
Using Crystal Reports version 14.0.3.613RTM

With SQL Server 2012 onward, if a field has both number and string data, I can use try_convert to compare, i.e.

TRY_CONVERT(int, resource_code) > 999999999

How do I handle this in Crystal Reports?

I tried:

ToNumber({sch_resource.resource_code})

But if resource_code isn't a number, CR barfs.


texts: Can someone decipher this deadlock for me?

texts:
We don't have a real DBA in our shop at this time, I'm filling in the best I can.  It looks like the INSERT has taken place first and is holding a lock the SELECT FOR UPDATE needs.  But the INSERT is also waiting on another lock, is that other lock already held by the SELECT FOR UPDATE, or is there another transaction not being shown in INNODB STATUS.

INNODB STATUS:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-11-24 11:42:33 0x1470
*** (1) TRANSACTION:
TRANSACTION 191471, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1136, 9 row lock(s)
MySQL thread id 7, OS thread handle 7700, query id 57301 localhost 127.0.0.1 resonant Creating sort index
select this_.Id as Id80_0_, this_.Queue as Queue80_0_, this_.OriginalQueueName as Original3_80_0_, this_.Message as Message80_0_, this_.Retries as Retries80_0_, this_.Locked as Locked80_0_, this_.Failed as Failed80_0_, this_.LastTryDate as LastTryD8_80_0_, this_.Context as Context80_0_, this_.CreateDate as CreateDate80_0_ from sys_messages this_ where this_.Queue<>'QDead' and this_.Locked=0 and this_.Failed=0 and this_.CreateDate<='2015-11-24 11:42:31.24' order by this_.CreateDate asc for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 675 page no 5 n bits 296 index idx_sys_messages_Locked_Failed_Queue of table `hmuw`.`sys_messages` trx id 191471 lock_mode X waiting
Record lock, heap no 226 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 00; asc  ;;
1: len 1; hex 00; asc  ;;
2: len 9; hex 515365727669636573; asc QServices;;
3: len 30; hex 323235383235656633623034643933343061303130613261326465353733; asc 225825ef3b04d9340a010a2a2de573; (total 32 bytes);

*** (2) TRANSACTION:
TRANSACTION 191469, ACTIVE 2 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 9
MySQL thread id 5, OS thread handle 5232, query id 57358 localhost 127.0.0.1 resonant update
insert into sys_messages (Queue, OriginalQueueName, Message, Retries, Locked, Failed, LastTryDate, Context, CreateDate, Id) values ('QEvents', 'QEvents', x'ACED000573720037636F6D2E7265736F6E616E742E6875622E6576656E74732E6E6F74696669636174696F6E732E4576656E744E6F74696669636174696F6E00000000000000010200024C0004646174617400104C6A6176612F7574696C2F4C6973743B4C0007737464446174617400454C636F6D2F7265736F6E616E742F6875622F6576656E74732F6E6F74696669636174696F6E732F4576656E745374616E646172644E6F74696669636174696F6E446174613B7870737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A657870000000157704000000157372003B636F6D2E7265736F6E616E742E6875622E6576656E74732E6E6F74696669636174696F6E732E4576656E744E6F74696669636174696F6E4461746100000000000000010200014C000673704461746171007E000178707371007E00040000000177040000000173720043636F6D2E7265736F6E616E742E6875622E6576656E74732E6E6F74696669636174696F6E732E4576
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 675 page no 5 n bits 296 index idx_sys_messages_Locked_Failed_Queue of table `hmuw`.`sys_messages` trx id 191469 lock_mode X locks rec but not gap
Record lock, heap no 226 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 00; asc  ;;
1: len 1; hex 00; asc  ;;
2: len 9; hex 515365727669636573; asc QServices;;
3: len 30; hex 323235383235656633623034643933343061303130613261326465353733; asc 225825ef3b04d9340a010a2a2de573; (total 32 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 675 page no 5 n bits 296 index idx_sys_messages_Locked_Failed_Queue of table `hmuw`.`sys_messages` trx id 191469 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 191 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 00; asc  ;;
1: len 1; hex 00; asc  ;;
2: len 7; hex 514576656e7473; asc QEvents;;
3: len 30; hex 396263626431636533623034636563383061303130613261326465353733; asc 9bcbd1ce3b04cec80a010a2a2de573; (total 32 bytes);

*** WE ROLL BACK TRANSACTION (1)
-


texts: Postgresql 9.3 replication: Preventing wal-files from building up, if receiver goes offline

texts:
I have a streaming replication running between two servers.

I just upgraded to postgresql 9.3 from 9.2. Earlier on in my 9.2 setup, I have tried that my slave server went offline due to a network outage. This resulted in unshipped wal-log files building up and taking up space VERY fast on my master server.

Can I prevent this in an easy way in 9.3?

I have seen the new wal_sender_timeout config, but I am unsure what exactly it does?

What's the best way of preventing wal-files from building up on sender if receiver goes offline in 9.3?


texts: Running sp_AskBrent with @ExpertMode = 1 in PowerShell

texts:
I am trying to run sp_AskBrent from PowerShell using Invoke-SQLCmd and capture its output in a variable:

$query = "EXEC SAIDBA.monitoring.sp_AskBrent @Seconds=10;"
$check = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query
             -ErrorAction Stop -ConnectionTimeout 3

When running with ExpertMode = 0, no problem.

But when running with ExpertMode = 1, we notice three things:

It outputs every data to the shell
$check is null
It ends with the following error:

  Invoke-Sqlcmd: Duplicate column names are not permitted in SQL PowerShell. To repeat a column, use a column alias for the duplicate column in the format Column_Name AS New_Name.
  At line:1 char:18

  $check = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -Er ...
  CategoryInfo: SyntaxError: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
  FullyQualifiedErrorId : DuplicateColumnNameErrorMessage, Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I think there is a workaround of providing parameters for sp_AskBrent to store the ExpertMode data into tables and select from those tables afterwards, but I want to make sure there is no way to get everything back at once in PowerShell.


texts:
When splitting out a data file into separate files is there any benefit in having tables usually queried together in the same file?

I'm against it but a vendor has suggested it as part of setup. May help with performance, the DB is currently 400 GB. The files will be on the same drive as well. This will just be a test, so I will split the file on a similar server and then run some tests to see if any performance gain is there. But when splitting the file should tables queried together be kept in (if possible) the same file?

This won't be different disks so not sure if we'll see any benefit from this, the data file is on 4 SSD drives in RAID 10 . I just need to split the file and as I have no idea how the vendors DB is queried without doing extensive monitoring not sure how to group the tables in different files.

I am referring to .ndf files (filegroups). Is there a best practise in allocating tables to filegroups?


texts:
How can I make below query work for virtual attached HDD. As of now this query return only C drive even though I have other drives also.

SELECT DISTINCT dovs.volume_mount_point AS Drive,
CONVERT(decimal,dovs.available_bytes/1048576/1024) AS FreeSpaceInGB,
convert(decimal,dovs.total_bytes/1048576/1024) as TotalSpaceInGB,
cast(CONVERT(decimal,dovs.available_bytes/1048576/1024)/convert(decimal,dovs.total_bytes/1048576/1024)*100 as decimal(38,2)) as FreeSpaceInPct
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInGB ASC


texts:
Consider tables cats and dogs in ani animals schema.
During sequences creation, sequence for cats.id used schema, whereas sequence for dogs.id didn't use schema:
create sequence ani.cats_id_seq
create sequence dogs_id_seq

How can I modify the dogs_id_seq so it also uses schema?
For example: ALTER SEQUENCE dogs_id_seq RENAME TO ani.dogs_id_seq this won't work.


texts:
I'm setting up a ManageIQ appliance for development.  When I updated the 'v2_key' encryption key to be same as production, my db connection croaked.  Initially I got these errors:
Aug 31 13:26:59 miq-dev.chq.ei sh[3642]: PG::ConnectionBad: FATAL: password authentication failed for user "root"

Aug 31 13:26:59 miq-dev.chq.ei sh[3642]: FATAL: no pg_hba.conf entry for host "::1", user "root", database "vmdb_production", SSL off

I updated my pg_hba.conf to:

# TYPE  DATABASE    USER  ADDRESS METHOD
local   all     all             trust
local   all         all           peer map=usermap
local   replication all           peer map=usermap
hostssl all         all   all     md5
host    replication all   all     md5
::1     vmdb_production root            trust        # <--- added last line

Now I get a new set of errors:
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2020-08-31 18:39:44 EDT; 49s ago
  Process: 3648 ExecStart=/usr/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 3645 ExecStartPre=/usr/libexec/postgresql-check-db-dir %N (code=exited, status=0/SUCCESS)
 Main PID: 3648 (code=exited, status=1/FAILURE)

Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on IPv4 address "0.0.0.0", port 5432
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on IPv6 address "::", port 5432
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on Unix socket "/var/run/postgresq...QL.5432"
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  redirecting log output to logging collector process
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:HINT:  Future log output will appear in directory "log".
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: postgresql.service: main process exited, code=exited, status=1/FAILURE
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: Failed to start PostgreSQL database server.
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: Unit postgresql.service entered failed state.
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: postgresql.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

which tells me postgresql service is not running.
I tried the suggestion from this SO post: Postgresql12.3 wont start on boot, systemd by adding a few lines to my postgresql.service file, but I still get the same ol errors.
Does anyone have any suggestions?  Thank you


texts: How to sync easily between a master database from Google Cloud SQL and a master database from Amazon RDS

texts:
I've created a master - slave (replication) in Google Cloud SQL.
I've also created a master - slave (replication) in Amazon RDS.

I want to sync two master MysQL from Google Cloud SQL and Amazon RDS. What's the approach can be done?


texts: Why is PostgreSQL 9.5 not using my newest index for ORDER BY, even though it uses similar indices just fine?

texts:
(Follow up from this post: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?)

PostgreSQL 9.5.

I can't divulge full details, but table has 22 columns and 5 indexes:

primary key ('pk'), text (btree)
another text (btree)
a timestamp with time zone (btree)
a tsvector (gin)
my latest one, a bigint (btree)

(From the previous post you know that I was trying to avoid creating this additional column, and just use an expression index--adding two integer columns together--without success.  The bigint column here probably could have been just 'integer' but I made a mistake creating it; it took about an hour to add the column, populate it, and reindex, so I'm hoping this isn't relevant but mentioning it just in case it is.)

All are btree except the tsvector.

The following queries all take only 12ms and use just an Index Scan:

SELECT pk FROM table ORDER BY pk DESC LIMIT 10
SELECT pk FROM table ORDER BY text_column DESC LIMIT 10
SELECT pk FROM table ORDER BY timestamp_column DESC LIMIT 10

But if I try to use my new bigint index for ORDER BY:

SELECT pk FROM table ORDER BY bigint_column DESC LIMIT 10

...it takes 2.7s and uses Limit -> Sort -> Seq Scan.

My "cheating" method is the closest I seem to be able to get to making use of the index:

SELECT pk
FROM table
WHERE bigint_column > 1000000
ORDER BY bigint_column DESC LIMIT 10

This takes 12ms and uses Limit -> Sort -> Bitmap Heap Scan (bigint_column > 1000000) -> Bitmap Index Scan (bigint_column > 1000000).

This is after a VACUUM ANALYZE after adding the index.

I thought it strange that my expression index was not being used in the other question.  Now it's just a plain old column (I haven't even added what would be the necessary triggers to actually go this route.)

Why isn't my newest index being used, when the other three work "just fine"?  (As pointed out in the comments at https://dba.stackexchange.com/a/183290/28774 , an Index-Only Scan would be even better.  I fail to see why all of these queries wouldn't use at least an Index Scan, let alone an Index-Only Scan, instead of a full Seq Scan.)

The index definition has DESC NULLS LAST (although it's a non-nullable column.)


texts: Improving PostgreSQL cluster performance

texts:
I am trying to cluster my table faster. The table is 27 GB worth of data, and has one index using a point geometry which is 19 GB in size. I am seeing about 5.5 hours to cluster the table.

I am assuming clustering is a IO heavy process, so anything to improve read/write times (new disk) would be my first thought, but are there any other things that could improve the time it takes to cluster a table?

I ran this test on a machine with an SSD and 12 GB of RAM, using postgresql 9.4


texts: Barman: What's the best way to switch from backup_method postgres (streaming) to rsync (ssh)?

texts:
My production postgresql server is currently backed up with barman using the backup_method postgres (streaming server). I would like to switch to rsync method (ssh) to benefit from better compression. Do I have to stop current process before reconfiguration, or can I modify the setup and activate it while running?

Current streaming setup:

conninfo = host=conn_host user=conn_user dbname=conn_db
streaming_conninfo = host=conn_host user=streaming_user
backup_method = postgres
streaming_archiver = on
slot_name = barman
minimum_redundancy = 3
last_backup_maximum_age = '3 DAYS'
retention_policy = 'RECOVERY WINDOW OF 31 DAYS'

Future ssh setup:

ssh_command = ssh postgres@conn_host
conninfo = host=conn_host user=conn_user dbname=conn_db
backup_method = rsync
reuse_backup = link
archiver = on
network_compression = true
minimum_redundancy = 3
last_backup_maximum_age = '3 DAYS'
retention_policy = 'RECOVERY WINDOW OF 31 DAYS'
wal_retention_policy = main


texts: Unknown Db file format - best way to find out?

texts:
A client has given me the data he wants visualised in my project - but I don't know what DB format it's in. He's pretty unclear, also..
I have access to the table structures, but don't know how to read it in. It's definitely not a flat file.

There are several files for each dataset:

file.dat (biggest by far)
file.id (small)
file.ind (half the size of *.dat)

The first few bytes in the *.dat file are 03 6E 02 14 14 15 19 00 (in case magic numbers are used).

Do these extensions ring any bells? Is there some software to determine the format?


texts: Move result of a query from one server to another table on another server

texts:
What are my options in moving result of a query in one server to another table on another server? Data contains ~400.000 rows so it won't be a quick one. (Both of them are SQL Server 2008 R2)

Thanks all.


texts: I looking for a solution to design database for property real estate

texts:
I looking for right method how can I design part of database schema for my  real estate website

I have table "proprety_real_estat" contain  Country, city, town and type of property  (screenshot 01)

screenshot 01

my problem:

I want to add many others fields home features checklist below

Wifi (yes/no)
TV(yes/no)
Gas(yes/no)
Electric(yes/no)
Garage(yes/no)
Parking(yes/no)
Balcony(yes/no)
...
...
and many others (screenshot 02)

screenshot 02

my quesitons :
01- if I add all this fields in the same table "proprety_real_estat" ??
02- if I make in JSON file after that I will save the path of this file in field ??

else if there are a better solution than mine ??

thank you in advance


texts: Set an SSIS SQL Agent job to unexpected error or failure

texts:
I was running a series of SSIS packages in a SQL Agent job on a virtual server when the server was shutdown - hard.

The package execution report shows it as still running even though I believe it is not. I believe this is because the Agent was not able to write whatever end state record which was required for it to show as "Unexpected termination".

How do I verify that it is, in fact, not still running?
How do I tell the SSIS Reports > All executions that it is not running?

Can I update or insert a record into an SSISdb table?


texts: International Components for Unicode (ICU) Date Formatter for MySQL?

texts:
Regarding to ICU - International Components for Unicode definition:

  ICU is a mature, widely used set of C/C++ and Java libraries providing Unicode and Globalization support for software applications. ICU is widely portable and gives applications the same results on all platforms and between C/C++ and Java software.

one of important part is formatting:

  Formatting: Format numbers, dates, times and currency amounts according the conventions of a chosen locale. This includes translating month and day names into the selected language, choosing appropriate abbreviations, ordering fields correctly, etc. This data also comes from the Common Locale Data Repository.

which implemented in vary languages; I would like to know if there is any implementation or work for MySQL to add this feature for Unicode date formatting?


texts: postgresql function index not being used by query

texts:
I am trying to match a text field so I created an md5 function index as it suggested since a full index was not allowed because the text data was too large. But I can't understand why postgres is not using the index. I am using PostGIS 2.2.1 and Postgresql 9.5.0 but I have other function indexes which do use the index.

production=# set enable_seqscan = off;
SET
production=# CREATE INDEX searches_helper_query_index ON searches(md5(helper_query));
CREATE INDEX

production=# \d searches;
Column         |            Type             |                       Modifiers
helper_query   | text                        |
Indexes:
 "searches_helper_query_index" btree (md5(helper_query))

production=# EXPLAIN SELECT  id FROM "searches" WHERE md5(helper_query) = md5('abcd');
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on searches  (cost=10000000000.00..10000017792.92 rows=1631 width=4)
   Filter: (md5(helper_query) = 'e2fc714c4727ee9395f324cd2e7f331f'::text)
(2 rows)

The table has about 7000 rows and many other columns and indexes. I only included the relevant column.

I ran vacuum verbose analyze on the table but it does not change anything either.

I also tried USING hash since I only need equality but it did not help either.

"searches_helper_query_index" hash (md5(helper_query))

As a comment this seems to only be with Postgresql 9.5.0. Either PostGIS or regular Postgres. My development machine has 9.5.1 and does not have this issue.

 dev=# EXPLAIN SELECT  id FROM "searches" WHERE md5(helper_query) = md5('abcd') ;
                                     QUERY PLAN
 Index Scan using searches_helper_query_index on searches (cost=0.27..8.29 rows=1 width=4)
 Index Cond: (md5(helper_query) = 'e2fc714c4727ee9395f324cd2e7f331f'::text) (2 rows)


texts: How to use COLUMNS_UPDATED to check if any of certain columns are updated?

texts:
I have table with 42 columns and a trigger which should do some stuff when 38 of these columns are updated. So, I need to skip the logic if the rest 4 columns are changed.

I can use UPDATE() function and create one big IF condition, but prefer to do something shorter. Using COLUMNS_UPDATED  I can check if all of certain columns are updated?

For example, checking if column 3, 5 and 9 are updated:

  IF
  (
    (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
     AND
    (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1)
  )
    PRINT 'Columns 3, 5 and 9 updated';

So, value 20 for column 3 and 5, and value 1 for column 9 because it is set in the first bit of the second byte. If I change the statement to OR it will check if columns 3 and 5 or column 9 is/are updated?

How can apply OR logic in the context of one byte?


texts:
I've a table which contains text data containing a bunch of translations for different language. Each translation is for a specific label.

I need to generate a pivot table so to get quickly what's missing.

An example of the records is

1, en, hello
1, fr, bonjour
1, es, hola
2, en, how are you
2, fr,
3, es, come es stas

Although translations should be always there for all language, I'm not 100% sure this is the case. So missing fields have to be considered.

The desired outcome is this

|ID|EN|FR|ES|
|1|hello|bonjour|hola|
|2|how are you| |come es stas|

The challenge I have is that the column order may not always be the same when the database is populated, so in theory I should have a dynamic list of fields.

There is not a direct PIVOT function in SQLite, so I started experimenting with the group_concat obtaining a comma separated string.

SELECT DISTINCT language, group_concat(word, ',') OVER (PARTITION BY language) AS group_concat
FROM vocabulary;

I can ran past the results later on in Python if needed; the issue is that any missing value is not appending an empty item thus shifting all the concatenation by n, thus making this solution not valid.

I have attempted also to use the filter clause in the select predicate (though this mean hardcoding the columns), but I was not able to succeed.

Any idea on how this can be achieved?


texts:
I am designing a database for a Soccer club. A soccer club has several teams. Those teams have several players and a coach.

Right now I have three tables:

Club
Team (foreign key to club)
Player (foreign key to team)

My main concern is that, each season a Team brings in new players and therefore, I need somehow to state that. I want to be able to maintain a record of the Team along the different seasons, and also be able to know what a Team is in the current season. How could I model that?


texts: sum of count in where clause (or having) in the same table with group by

texts:
I have a products table with columns id, binno, name, sku, inwarddate and more.

Each bin stores 20 or 30 products. We need to group by binno for dispatch and show sum of items in each bin. That makes the query look like:

SELECT  count(*) as total,binno, model, color, sku FROM products
 where location=1 and sku='4'
 GROUP BY binno

This is working. It gives the following result:

Binno model color sku total
231    3    2      4   20
232    3    2      4   20
...

Now I need to select bins where total of this count=500 (or any other number) to limit the results to show 25 bins. Required number may not be 500. it may be any number but the user knows the no of items in a bin (just like ATM user).

I need sum of count(*) in having or where. That will solve my problem.


texts: Will hacking away ibdata1 destroy databases with only MyISAM tables?

texts:
I made a mistake.

I have a huge database of MyISAM tables that I was playing with converting [a copy of] to InnoDB. I didn't set innodb_file_per_table=1 before doing so.

I know that safely shrinking ibdata1 requires all databases to be nuked then reloaded from an SQL dump.

But, if I don't care about my InnoDB tables any more, can I remove the ibdata1 file without breaking the original database that only contains MyISAM tables? Dropping it and reloading it from an SQL dump is kind of possible, but since the database is approaching 250GB in size, it will take a lot longer than I'd like.

$ mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i686) using readline 5.1
$ cat /etc/issue
Red Hat Enterprise Linux Server release 6.5 (Santiago)


texts: MYSQL 5.7 Note Got an error reading communication packets

texts:
134359 [Note] Got an error reading communication packets

Appears in logs every 5 minutes, I have seen a few similar posts on here however none actually match the error I'm seeing here.

MySQL 5.7 - configuration:

[mysqld]
##skip-name-resolve=1
#bind-address=localhost
default-storage-engine=InnoDB
innodb_file_per_table=1
performance-schema=1
server-id=2
auto_increment_offset = 2
auto_increment_increment= 5
report-host=2

log-slave-updates
##
replicate-ignore-db=mysql
replicate-ignore-db=cphulkd
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
##

max_connections=1000
table_open_cache=2000
wait_timeout=86400
interactive_timeout=86400
net_read_timeout=28800
net_write_timeout=28800
table_definition_cache=2000
thread_cache_size=100
long_query_time=10000
log_queries_not_using_indexes=0
innodb_page_cleaners=128
sql_mode=""
max_allowed_packet=512M
local-infile=0

[mysql]
# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 10M
##myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 10000M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 0

# CACHES AND LIMITS #
tmp-table-size                 = 100M
max-heap-table-size            = 100M
query-cache-type               = 0
query-cache-size               = 0
#max-connections               = 1000
thread-cache-size              = 50
open-files-limit               = 1024
table-definition-cache         = 4096
table_open_cache               = 2024

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 10G
innodb_log_buffer_size         = 5G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 80G
innodb_buffer_pool_instances   = 64
innodb_write_io_threads        = 64
innodb_read_io_threads         = 64
innodb_max_dirty_pages_pct     = 90
innodb_io_capacity             = 1000
innodb_stats_on_metadata       = 0
innodb_adaptive_flushing       = 1
innodb_lru_scan_depth          = 1024

# LOGGING #
log-error                       = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes   = 0
slow-query-log                 = 0
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
join_buffer_size                = 55M
binlog_cache_size               = 16M
key_buffer_size                 = 50M

The thread ID 134359 doesn't correspond to any actual threads if I enable General MySQL Logging, nor does anything appear in a tcpdump during the events occuring.

At a loss? I do feel it's something CPanel or WHM related


texts:
I apologize if this question is elementary; I am new to Database administration.

I have a database on my home computer (iMac) that I would like to be able to access from anywhere (on any OS).  Specifically, I need to be able to query my data from any other computer.

How can I do this?


texts: Use of IMPERSONATE permissions in SQL Server?

texts:
During some reading, got to know about the Impersonate permissions. From what I've read, it is more like creating a copy of the user with all the permission levels under a different name. I understand that this can be used for executing any queries under a different login but ultimately what purpose does it serve ?

Why was this feature introduced?


texts: Best way to handle column that doesn't conform to one particular type

texts:
I have a Settings table:

CREATE TABLE `Settings` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Code` varchar(100) NOT NULL,
  `Name` varchar(250) NOT NULL,
  `Description` text NOT NULL,
  `Default` varchar(30) NOT NULL, <<<<<<<
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The Default value may however be integers, booleans, text, enums etc. etc.
I have set it to varchar right now, and when retrieving the setting, I will know what setting it is, e.g Send me daily emails - True/False, and do a case at the application level given that I know the type the value should be for that particular setting.

Is there a better way of doing this?


texts: Choose two columns for same purpose or it should be two different columns?

texts:
We are an ecommerce website. We provide Finance option for our stocks, we have these finance options for few stocks. We have a stock table which has two columns:

IsFinanceAvailable: true/false. This tells if stock has emi available or not.
EmiAmount: This gives emi amount if its applicable. The stock may have finance but need not have emi.

We may have a case where IsEmiAvailable is true but EmiAmount is not available, but if EmiAmout is available, IsEmiAvailable has to be set true.

Should we have two separate columns or infer all info just with EmiAmount. For example:

EmiAmount null: This means no finance

EmiAmount 0: This means finance available but no emiAmount

EmiAmount>0

Should we have two different columns or just emi amount would do? what are pros and cons?


texts: User-defined harmonic mean function performs worse than query in PostgreSQL 9.6

texts:
We created a harmonic mean function based on this blog post:

CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric)
RETURNS numeric[]
LANGUAGE sql STABLE COST 1
AS $h$
SELECT array[$1[1]+1.0/$2, $1[2]+1.0];
$h$;

CREATE OR REPLACE FUNCTION public.harmean_finalize(numeric[])
RETURNS numeric
LANGUAGE sql STABLE COST 1
AS $h$
SELECT $1[2] /  $1[1];
$h$;

CREATE AGGREGATE public.harmean(numeric) (
    sfunc = public.harmean_accum,
    stype = numeric[],
    finalfunc = public.harmean_finalize,
    INITCOND = '{0.0, 0.0}'
);

But when we compare the performance of using this user-defined function in a query, it's >6x worse:

SELECT category, harmean(spd)
FROM spds
GROUP BY category

vs

SELECT category, COUNT(spd)/SUM(1/spd)
FROM spds
GROUP BY category

The EXPLAIN ANALYZE doesn't really give insight into what might be slowing down the user-generated aggregate.


texts: What does WITH FORMAT do on a DIFFERENTIAL backup?

texts:
I could find a lot of questions about this. This is a really good question, exactly what i'm asking here.

Down there, a guys asks the question with:

  you can have compressed and uncompressed on the same drive, just not
  the same media set (file).

I planning to create a simple backup routine where There's a full backup at night, and a differential backup each hour. But to use COMPRESSION in a differential backup, I need to use FORMAT. What does this mean?

  Use FORMAT carefully. Formatting any volume of a media set renders the
  entire media set unusable. For example, if you initialize a single
  tape belonging to an existing striped media set, the entire media set
  is rendered useless.

I can't understand this. Every backup has a different name, and they're going to stay in the same drive. What am I going to format with this option?

Can I have:

FULL.BKP
DIFF1.DIFF( With compression,format)
DIFF2.DIFF( With compression,format)
DIFF3.DIFF( With compression,format)
DIFF4.DIFF( With compression,format)
DIFF5.DIFF( With compression,format)

in the same disk? Thanks.


texts: Query behavior - with regard to statistics

texts:
A quick background of the issue: We have an application, and we have many instances of this application running for clients. While they may be on slightly different versions, they are fundamentally the same.
Yesterday, one client had an issue with SQL timeouts. Looking at the query we identified an issue with certain tables, and the use of OUTER APPLY and re-wrote it to circumvent the issue.
Inspecting the query plan today, I can clearly see that the stats are bad, as it is expecting circa 2.5 million rows, which is incorrect. I updated the stats and it has resolved the issue, and now expects 30 rows.
My confusion comes from when I inspect the query plan for other clients' databases, and the stats seem off, but, the query is returned in around 1 second, not the 45 seconds seen in the issue that was faced.
Both databases have auto stats turned on. Does this suggest a problem with auto stats on the problem database?
Whilst testing, I did clear the cache DBCC FREEPROCCACHE so the engine had to generate a plan each time. I have not done this on a database that was returning the data in a timely fashion however.
Sorry for the vagueness, I unfortunately cannot share the query plan, due to sensitive information.
Currently, we run only automatic stats updates (no scheduled statistics / index maintenance). This will change; the databases have been neglected somewhat. I should also mention, these databases are in Azure. I am not sure if that changes anything?


texts: Dump of a mysql table on import replaced existing records

texts:
I took a dump using mysqldump..

mysqldump -u... -p... mydb t1 > mydb_table.sql

Then I imported the dump in another database having same table, but different records..

mysql -u...-p... mydb < mydb_tables.sql

The importing db had records from primary_key 1 to 1000, and the exporting db had 5000 to 10,000..

But on import the existing records, ie 1 to 1000 got deleted..

How?? Why?? If it a default behaviour, what options I can give to dump to not let it happen next time..


texts: What is the correct way to make an exact copy of a database with pg_dump?

texts:
Basically, I'm trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc.
Is this doable with pg_dump or does psql need to be used instead?
I've been using pg_dump -U user -cOx database > pg.sql, but it isn't entirely clear to me if it captures everything.
I was looking at all of the pg_dump flags here:
https://www.postgresql.org/docs/11/app-pgdump.html
The only one I can think that might do it, because it doesn't really say explicitly, is the -s --schema-only flag, which excludes data. I'm fine with that and running a second pg_dump for the data, but I primarily want to make sure I am backing up the user defined functions, relationships, etc.


texts: Clustered Index 'Seek predicate' and 'predicate' on the same column

texts:
I have a clustered index primary key column and I'm doing a range query on it.
The problem is the scan only uses the first range part for the seek predicate, leaving the other side of the range as a residual predicate.
which causes to read all the rows up to the @upper limit

I'm using two parameters for the range:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from messages
where msg_id between @lower+1 and @upper;

In that case the actual execution plan shows:

predicate: messages.msg_id >= lower
seek predicate: messages.msg_id < @upper
rows read: 1005

Table definition (Simplified):

CREATE TABLE [dbo].[messages](
    [msg_id] [numeric](18, 0) IDENTITY(0,1) NOT NULL,
    [col2] [varchar](32) NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
    [msg_id] ASC
)

More information

When used with constants instead of variables both of the predicates are 'Seek'
Have tried Option (Optimize for (@lower=1000)), without success


texts: What can I do with VIEW DEFINITION permissions that I can't do with ALTER permissions?

texts:
I have created a new user which has ALTER permissions on my database:
CREATE LOGIN [testuser] WITH PASSWORD = '...';
CREATE USER [testuser] FOR LOGIN [testuser];
GRANT ALTER TO [testuser];

To my surprise, ALTER does not imply VIEW DEFINITION:
SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'ALTER'); -- yields 1
SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'VIEW DEFINITION'); -- yields 0

And, indeed, according to the documentation, VIEW DEFINITION is only implied by CONTROL. So, apparently, VIEW DEFINITION includes some rights that ALTER doesn't.
Can someone give me an example of what can be done with VIEW DEFINITION permissions which cannot be done with ALTER permissions?
I did some tests (read column default values, script database object definitions), and I was unable to find an example myself - everything I tried "just worked" with ALTER permissions.


texts: Alternatives to User Defined Performance Counters for CPU Monitoring - SQL 2014

texts:
I want to monitor CPU usage before implementing TDE on several production SQL Servers.

My plan was to use user defined performance counters, over a period of a couple of days, counters as defined here: https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-cpu-usage?view=sql-server-2017

I can view data on these counters when just looking at the Performance Monitor graph but the Data Collector sets just show datetime stamps and no counter data.

I'm on a time sensitive project and so I am looking for an alternative to perfmon.... and any advice.

I suppose the cause of the empty counters belongs on serverfault - but I would appreciate a solution if anyone ran into this issue and solved it.


texts: ON DUPLICATE KEY UPDATE faster than UPDATE


texts: Disable Journaling in MongoDB + WiredTiger

texts:
We have a cluster of 3 Mongo 3.2.13 instances running with journaling enabled, with hundreds of DBs and collections which are accessed simultaneously.

We are checking the option to disable journaling, to improve the cluster's performance. According to the docs, WiredTiger performs a checkpoint every 60 seconds, so in a case of crash we should only loose up to 60 seconds of data. We are ok with that.

Are there any other risks / disadvantages for disabling journaling?


texts: How to migrate from a MySQL master-slave setup to a master-master setup without downtime

texts:
We have an existing master-slave MySQL replication setup where we would like to promote the second slave to a master in order to achieve a master-master setup with no or only minimal downtime.

Is it sufficient to do a SHOW MASTER STATUS on the slave and do a CHANGE MASTER TO ... on the current master or will this result in a replication error?


texts: Database schema for large possible product combinations and associated price

texts:
I'm new to database schemas and need some help.

I have to draw up a database schema from a large, (2601) possible product combination, with width and height and associated price.

Is creating columns form all the combinations the correct way?


texts: SQL Server 2016 Altering avaibilty group listener IP

texts:
I cannot find the right method/way/syntax to change my IP listener address on an Always On availability group under SQL Server 2016.
I have a listener, myldag on port 1433 IP 150.1.2.3 and I want to change this IP to 150.2.4.5
Using SSMS I can add an existing one using the following SQL
ALTER AVAILABILITY GROUP [mydag]
MODIFY LISTENER N'myldag ' (ADD IP (N'150.2.4.5', N'255.255.255.0')

Is there a way to add a listener via SSMS?
Can someone please help me  ?


texts: Using the value of a JSON object that is stored in a PostgreSQL JSON array

texts:
I have a table that looks like this (simplified):

Jobs
-------------------------------
ID | Details
-------------------------------
1  | { 'tasks': [] }
2  | { 'tasks': [{'id': '1', 'due_on': '2015-06-8'}, {'id': '2', 'due_on': '2015-06-09'}] }
3  | { 'tasks': [{'id': '3','due_on': '2015-06-8'}] }

I am trying to query for two different things, both based on the due_on date in the subtasks:

The job id of all jobs that have a task due on a specific date
The tasks that are due on a specific date along with their job id

With a normalized structure these would obviously be easy queries. In this case, the data is structured as a JSON object for a very specific reason that's out of this question's scope. Let's just go with it.

I have been experimenting with the JSON functions and the array functions but I just can't seem to get the query right.

For example, SELECT * FROM Jobs WHERE Details->'tasks'->>'due_on' = '2015-06-08' just throws a cannot extract field from a non-object error (for pretty clear reasons).

Alternatively, I could store the data as a tasks object instead of an array. If the above would be much easier / faster / better then I'll refactor.

Any help would be much appreciated. Thank you.


texts: Why ALL PRIVILEGES ON `db`.* gives access to all databases?

texts:
I've installed Ubuntu 12.10(quantal) and mysql-server-5.5.

When I execute:

drop user ''@'localhost';
create database `redmine`;
grant all on `redmine`.* to 'redmine'@'localhost';

User redmine@localhost can access not only redmine database, but also other databases.

How do I prevent him from accessing any database except redmine?

P.S.
I can't reproduce this on other distributions. What is a proper behaviour? Is this a bug?

UPDATE1:

mysql> show grants;
+--------------------------------------------------------------+
| Grants for redmine@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'redmine'@'localhost' |
| GRANT ALL PRIVILEGES ON `redmine`.* TO 'redmine'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| x |
+----------------+
1 row in set (0.00 sec)


texts: Barman not able to connect: fe_sendauth: no password supplied

texts:
I'm trying to setup streaming replication to a Barman server.

Barman config is pretty much basic, I'm using backup_method = postgres.

[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log
log_level = DEBUG
archiver = true
backup_method = postgres
backup_options = concurrent_backup
compression = gzip
minimum_redundancy = 1
retention_policy = RECOVERY WINDOW OF 7 DAYS
retention_policy_mode = auto
streaming_archiver = true
wal_retention_policy = main
configuration_files_directory = /etc/barman.conf.d

I'll call the DB server pg

[pg]
description = "pg"
ssh_command = ssh postgres@pg.example.com
conninfo = user=barman dbname=postgres host=pg.example.com port=5432

I've configure hba rules and setup a .pgpass file in barman's home /var/lib/barman/.pgpass.

A test connection to pg server works just fine:

barman@b01:~$ psql -c 'SELECT version()' -U barman -h pg.example.com postgres
                                                             version
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 (Debian 10.7-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

However barman check pg fails miserably and I have no idea why:

$ barman check pg
Server pg:
        WAL archive: FAILED (please make sure WAL shipping is setup)
        PostgreSQL: OK
        is_superuser: OK
        PostgreSQL streaming: FAILED (fe_sendauth: no password supplied)
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
        pg_basebackup: OK
        pg_basebackup compatible: FAILED (PostgreSQL version: None, pg_basebackup version: 11.2-1.pgdg90+1))
        pg_basebackup supports tablespaces mapping: OK
        archive_mode: OK
        archive_command: FAILED (please set it accordingly to documentation)
        pg_receivexlog: OK
        pg_receivexlog compatible: FAILED (PostgreSQL version: None, pg_receivexlog version: 11.2-1.pgdg90+1))
        receive-wal running: FAILED (See the Barman log file for more details)
        archiver errors: OK

Is there a better way how to debug authentication issues? Is the .pgpass even loaded under barman check?

pg.example.com:5432:postgres:barman:s3cr3tP2ssw0rd


texts: Variables in DB data to avoid redundancy?

texts:
"Avoid redundancy" is important to me.

I want to store the input data for configuration management in a relational database.

My input data:

project_name
linux_user
db_user
db_name

In 95% of all cases all attributes are equal:

project_name: foo
linux_user: foo
db_user: foo
db_name: foo

If I would store this data in YAML, then I could use variables to avoid repetition.

AFAIK this does not work in relational databases.

I could store this in DB, but AFAIK I need to evaluate it myself:

project_name: foo
linux_user: {project_name}
db_user: {project_name}
db_name: {project_name}

How can I avoid redundancy if I store this in a relational database? Unfortunately these values are not always equal, only in most cases.

I use PostgreSQL


texts: Can this query be optimised with indexes, or should I change my schema?

texts:
I have a MySQL table that stores each time a user has downloaded a photo that they've bought.  I want to be able to retrieve the first time each photo was downloaded:

SELECT MIN(downloaded) AS downloaded
     , photoId
     , size
     , transactionId

FROM ORDER_DOWNLOADS

GROUP BY photoId, size, transactionId

I've got an index on photoId, size, transactionId, downloaded, as well as single-column indexes on each column.

With 50,000 rows this takes around a second.

Can I simply improve it substantially with a rewrite or a better index, or is my best bet to add a "firstDownload" bit column that I set to 1 upon an initial download and 0 on any subsequent downloads of the same photo/size/transaction?

Thanks!

explain:

id 1
select type SIMPLE
table ORDER_DOWNLOADS
partitons null
type index
possible keys photoId, size, transactionId, downloaded, covering
key covering
key_len 87
ref null
rows 43878 (I was being approximate when I said 50,000 earlier, this is the whole table)
filtered 100
Extra Using index