247
Add or edit the following line in your postgresql.conf :
listen_addresses = '*'
Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 0.0.0.0/0 md5
Restart Postgresql after adding this with service postgresql restart or ...
25
This solution works for IPv4 / IPv6
nano /var/lib/pgsql/data/pg_hba.conf
add at final
host all all ::1/128 md5
host all postgres 127.0.0.1/32 md5
and then restart postgresql service
/etc/init.d/postgresql restart
24
The option max_user_connections is a limit imposed, not on the total number of simultaneous connections in the server instance, but on the individual user account.
Let's say the user is called db_user@localhost. You can find out what this user's connection limit is by running this query:
SELECT max_user_connections FROM mysql.user
WHERE user='db_user' AND ...
15
Use an array to represent the series of values:
pg_prepare($con, "prep", "select * from test where tid=ANY($1::int[])");
$strpar = "{3,4,6,8,10}";
pg_execute($con, "prep", array($strpars));
The cast to int[] in the query might even be superfluous if the planner is able to infer the type by itself.
13
If you want to use reserved words as table or column names, you have 2 options:
use brackets (the SQL-Server's way): SELECT [Kill]
or double-quotes* (the ANSI/ISO standard): SELECT "Kill"
Your whole statement would become:
SELECT [serial], [Kill]
FROM tbl_pvporderview
WHERE [Kill] > (?)
ORDER BY [Kill] DESC ;
*: Of course, using double quotes (...
13
The solution to your problem is a MySQL capability called "partitioning". The documentation is here.
What partitioning does is store a single table in separate "partitions". These are defined by a particular expression, usually a column value or range. In your case, this would probably be based on endTime -- assuming that it is known when a record is ...
13
The way I solved this was:
Added the line as below in pg_hba.conf:
hostnossl all all 0.0.0.0/0 trust
and this was modified in postgresql.conf, as shown:
listen_addresses = '*'
I had this instance running on a Centos 7.3 and Postgres 9.5 in a VM in Azure, given this was a POC (proof of concept) you won't want to connect ...
12
Since you have more writes then reads, I would like to recommend the following
Decent Tuning of InnoDB would be the key
Buffer Pool (Sized by innodb_buffer_pool_size)
Since InnoDB does not support INSERT DELAYED, using a large InnoDB Buffer Pool is closest thing you can get to INSERT DELAYED. All DML (INSERTs, UPDATEs, and DELETEs) would be cached in the ...
12
First you need to do is run this query:
SELECT user,host FROM mysql.user
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the ...
12
(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated):
Lots more features.
CHECK constraints
True SERIALIZABLE isolation
Arrays (including index support for arrays)
Window functions (lead, lag, row_number, etc)
Common table expressions (WITH queries) including recursive CTEs and writeable CTEs
...
11
The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. leave_type didn't seem relevant to the problem at hand.
CREATE TABLE dbo.EmpLeave
(
EmployeeID int,
leave_date date,
leave_type_color char(6)
);
INSERT ...
10
That is called a column alias.
An alias is used to give columns a new name for the result set. This can be used to name a column based on an expressions (because otherwise the expression wouldn't "have" a name) or to rename columns, e.g. because the select list contains columns from different tables with the same name:
select t1.foo as t1_foo,
t2....
answered Nov 24 '12 at 12:39
a_horse_with_no_name
53.9k10 gold badges106 silver badges136 bronze badges
10
I have always implemented a Soft Delete by including two additional columns in my tables, one for status and one for delete date.
My table structure would be similar to the following:
create table myTable
(
id int,
name varchar(50),
IsDeleted bit,
DeletedDate datetime
);
The IsDeleted column has a default value of no and the DeletedDate is not ...
9
Wrap the column name in square brackets:
$sql = "SELECT [Kill] FROM tbl_pvporderview";
7
From your description, you'll need the following tables ("..." means you might need additional fields in that entity):
User (UserID, EmailAddress, Password ...)
Session (SessionID, CreatedDate, UserID, ExpiryDate, PurchaseCompleteInd ...)
Product (ProductID, Name, UnitPrice ...)
SessionProductLink (ProductID, SessionID, Quantity ...)
The SessionProductLink ...
7
The question asked by yes123 is very relevant in the sense that we all want to know what we can do to improve MySQL's performance without touching the application that's consuming that data. What is that perfect configuration that will just make everything better? Although no one program can just spit out that perfect configuration, there are ways to ...
7
This is a good "there's more than one way to do it" question.
Start by thinking about your data usage:
Think about which fields are going to be read and written more often.
For example, first name and last name are probably written once and are rarely changed, but depending on your app, they could be read very often.
Last login date could be written ...
7
To expand more on @Twinkles alternative, I'm concerned about why you're storing present and absent students in your attendance data. Is it because you want to be able to run a report that shows all kids and whether they were absent on a given day? There's a better way to do it.
Most systems I've seen store either the that the student is present (Positive ...
7
This looks like you might have hit a bug logged against Percona Server 5.5:
Concurrent duplicate inserts can violate a unique key constraint in InnoDB tables.
There is no fix and no reproducible test case for this bug yet. It has only been observed in a production environment.
The pattern described is:
INSERT a value into a column with a unique constraint....
6
If you are doing a range of consecutive dates, there is no need to use the IN operator.
If you use this to compute midnight seven days ago
DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND
here is what you get back:
mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND;
+--------------------------------------------------+
| DATE(NOW()) - ...
6
You are looking for pt-archiver, part of the Percona Toolkit. The tool can purge rows from a table; write rows from a table to file; purge rows from a table while copying them to another table (possibly another server)...
It does so in chunks, so like 1,000 rows at a time, so that there is no high load on your server.
6
In addition to privileges on the TABLE, a role needs privileges for the SCHEMA and the DATABASE. The most likely candidate is the schema. If so, this additional GRANT would be the fix:
GRANT ALL ON SCHEMA my_schema TO my_user;
Or at least:
GRANT USAGE ON SCHEMA my_schema TO my_user;
6
I don't use phpPgAmin.
To change an existing column definition to use the CYCLE attribute you need to understand that this is an attribute of the sequence not the "column".
A serial or bigserial is only a shorthand notation to assign a default value to a column which is take from a sequence.
When you define a column as serial Postgres automatically ...
answered Feb 10 '14 at 7:27
a_horse_with_no_name
53.9k10 gold badges106 silver badges136 bronze badges
6
Fresh Postgres 9.5 install, Ubuntu.
The key was the local connection type, since psql uses domain socket connection.
pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all md5
host all all 0.0.0.0/0 md5
6
The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance metrics. You could do this by lowering the compatibility level to <120.
I would perform all this testing on a test server and not in production.
https://...
6
The foundation of your system is your database. If you change your database schema then you also have to change your code. That means that keeping a stable data model helps to reduce the amount of rework you need to do in your code.
Having said that, in the real world requirements change. It's a good idea to invest some time up front to get your data ...
6
When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data modification statements are run in autocommit mode by default so each statement is all-or-none.
6
You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR.
Additionally, if you stored it as VARCHAR then the format is baked into your data, which will make it harder for you to use different date formats in the presentation layer. For example, ...
5
Well you could use IBM DB2 LUW (Linux,Unix, Windows) Express-C edition. It is free and has community support. It is the same engine/binaries as DB2 Enterprise Edition, it just has certain features "turned off" and has memory and CPU caps, but for what you are describing, it may suit your needs. If you do find you need more memory/CPU you could always ...
5
You must bind the elements of an IN list, otherwise Oracle will consider these separate statements. Any differences in the text of the SQL will cause a new statement to be parsed (excepting some cases when cursor_sharing = force). Therefore your examples with :a, :b and :a, :b, :c will be considered different SQL statements.
Each element must be bound to a ...
Only top voted, non community-wiki answers of a minimum length are eligible
